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
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
Beautiful, just what I needed! Thanks!
Jim McNeely
March 15, 2012 at 9:45 pm
Thanks. Awesome blog. Very helpful!
Sachin Anand
May 16, 2012 at 6:40 am
It is a wonderful function, helped a lot
Giridhar
June 29, 2012 at 5:38 am
Wonderful!..It helped me to create a nice pivot table in mysql. Thanks a lot:)
Vijay P
July 15, 2012 at 8:30 am
[...] http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ [...]
Mysql | Rahul's Work
October 5, 2012 at 6:27 am
You are hero!!
Marco C.
October 23, 2012 at 2:22 pm
Very useful, thank you for posting it
Mark
October 24, 2012 at 4:37 am
I need to get multiple Rows Results ..it showing only selected (Pos)value..I want to Entire string value..I Expecting Result in multiple rows..if any one knows plz reply..
siva
October 31, 2012 at 4:21 am
Great function, solved a problem on multiple inserts from delimited string. Thanks very much for posting.
Colin W
November 3, 2012 at 2:15 pm
Does anyone have the code to do the same sort of string to column conversion in MSSQL.
Searching the net all I have found is UDF functions that split strings to rows – I need the data in columns I can name.
Grant
November 7, 2012 at 10:16 am
This works! Perfect! Thanks.
Gabriel Oliveira
December 8, 2012 at 11:57 am
You are just great thanks!!
Mannoj
December 13, 2012 at 3:03 pm
This is such a big help.. Thank you very much!
Thina
January 30, 2013 at 1:58 am
awesome function
ajit
March 6, 2013 at 1:51 pm