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 |
+-------+
Advertisement
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
Thanks a lot! I only know basic SQL so you really helped me out here!
Niklas Waller
November 18, 2010 at 10:46 pm
Simply perfect, concise what I appreciate most.
you saved at least one hour of my time.
Can I use it freely?
In case send me the copyright line you want me to add in my code.
Thanks
Claudio Nanni
March 5, 2011 at 8:10 pm
thanks i only change one thing (DETERMINISTIC) for avoid
“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)”
– SELECT split_str(‘a|bb|ccc|dd’, ‘|’, 3) as third;
CREATE FUNCTION split_str(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, ”)
If i am wrong please tell me.
Christian Eduardo Scolari
April 1, 2011 at 2:28 pm
nice function. exactly what I need.. :) thanks.
sukoshi
July 22, 2011 at 1:52 am
It is very very usefull. Excellent!
magento
August 15, 2011 at 7:01 am
thank you
janus
October 19, 2011 at 6:23 am
Hey Federico,
Thank you for your lovely function. I had only one problem with that, seems that LENGTH() returns (int)2 for two-bytes chars while CHAR_LENGTH() returns only (int)1, so when I tried to use:
select split_str(‘ABC¼,,BA,,abc’,',,’,3); //result: “,abc” (delimiter was still there)
When I replaced LENGTH() to CHAR_LENGTH() everything worked fine.
You may read about it here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_char-length
Do you mind that I will post your function with my fix on my blog? (with copyrights of course)
Shaked.
Shaked KO
November 14, 2011 at 7:41 pm
[...] Federico Cargnelutti – MySQL Split String Function Stackoverflow – MYSQL – Array data type, split string [...]
Shaked Klein Orbach
November 23, 2011 at 11:21 pm
I was using this function in older versions of mysql but now I changed to Mysql 5.5.11 on Windows and it’s running extremely slow. Anyone else with the same problem?
Golimar
December 9, 2011 at 5:33 pm
Thanks for the working function
But how can we pass a databse query to it?
Say for example I want to split domain name from email address stored on a table
I can show all email address by “Select Emai_Address From Email_Table” but if i want to split domain part of each email address is it possible by this function??
Naeem hasan
January 26, 2012 at 5:41 am
@naeem you need to create the function SPLIT_STR() before using it http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Federico
January 26, 2012 at 9:25 am
Sir.
You are a hero in my eyes =]
This function should come very much in handy!
Jon
February 2, 2012 at 9:11 am
Thanks,very useful
Mijo
February 4, 2012 at 10:48 pm