MySQL Split String Function
MySQL does not include a function to split a delimited string. However, it’s very easy to create your own function.
Create function syntax
A user-defined function is a way to extend MySQL with a new function that works like a native MySQL function.
CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
To create a function, you must have the INSERT privilege for the <mysql> database.
Split delimited strings
The following example function takes 3 parameters, performs an operation using an SQL function, and returns the result.
Function
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
Usage
SELECT SPLIT_STR(string, delimiter, position)
Example
SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;
+-------+
| third |
+-------+
| ccc |
+-------+
Worth noting that if you write an application that depends on a custom function like this, you should definitely let your system administrator know. I believe that the function definition is stored in the information_schema database on mysql, and if your system administrator isn’t backing up that database, you might get a nasty surprise if he ever has to restore from backups.
karlkatzke
February 23, 2009 at 1:39 am
True, thanks for pointing that out.
Federico
February 23, 2009 at 10:23 am
Have I read “worth nothing” ???
That’s a great function, very useful !!
Thanks for sharing Fédérico ! :-)
Erwan
March 24, 2009 at 1:26 pm
It may be nice to have the function stored if you think you will re-use it! I think I can cut your code like this:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘a|bb|ccc|dd’, ‘|’, 3), “|”, -1);
Only the value of “3″ needs to be changed to select an item in the list. “-1″ will never change.
Damien Goor
March 27, 2009 at 3:14 pm
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘a|bb|ccc|dd’, ‘|’, 9), “|”, -1);
dd
One might want check that the desired number of delimiters exists as well, e.g.:
SELECT ‘a, bb, ccc, dd’ RLIKE ‘((, ).*){3}’;
1
SELECT ‘a, bb, ccc, dd’ RLIKE ‘((, ).*){4}’;
0
Jussi
March 30, 2009 at 11:37 am
Thanks!!!!!
PEGG
March 30, 2009 at 1:24 pm
very helpful! thanks a lot!
randell
June 8, 2009 at 2:27 pm
Nice one! Thanks a lot!
Anton
August 7, 2009 at 8:39 am
Very useful!
rob
September 11, 2009 at 7:06 am
[...] http://www.ispirer.com/doc/sqlways39/Output/SQLWays-1-365.html http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ [...]
Split String no Mysql
October 10, 2009 at 2:44 pm
Nice function. Thanks
orice on
October 19, 2009 at 9:32 am
Thanx dear u solved my big problem thanx again.:-)
UFM
October 25, 2009 at 6:52 am
LENGTH(.. -> CHAR_LENGTH(..
gsa
November 25, 2009 at 3:41 pm
Very helpful!!!! Thanks a bunch.
Deepti
February 18, 2010 at 1:19 pm
Hello, very good function, do you allow me to write a post about this on my Blog with a reference to your Post at the end?
Alessandro De Carli
June 12, 2010 at 8:06 am
Yes, sure.
Federico
June 12, 2010 at 9:11 am
Very nice trick
Santhosh
August 19, 2010 at 11:12 am