MySQL string split function

split_str.sql

-- SPLIT_STR MySQL Function
-- from http://blog.fedecarg.com/2009/02/22/mysql-split-string-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, '');

/*
Example:

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+
| third |
+-------+
| ccc   |
+-------+
*/

Nice dude, thx for the script :)

Hi Mike !

Thank you very much :)

A Complete LifeSaver

it doesn't work

impossible to create that function in mysql 5x
#1418 - 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)

excellante .. works like a charm

Use CHAR_LENGTH instead of LENGTH for preventing wrong splitting.
Described here -> http://www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html

Do you have a version that returns all strings? eg:

SELECT SPLIT_STR_ALL('a|bb|ccc|dd', '|') as list;

or

SELECT value FROM SPLIT_STR_ALL('a|bb|ccc|dd', '|');

That would give:

+------+
| list |
+------+
| a    |
| bb   |
| ccc  |
| dd   |
+------+

@CC007

I also need this version