Skip to content

Instantly share code, notes, and snippets.

@mikedamage
Created January 15, 2010 03:24
Show Gist options
  • Save mikedamage/277782 to your computer and use it in GitHub Desktop.
Save mikedamage/277782 to your computer and use it in GitHub Desktop.
MySQL string split function
-- 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 |
+-------+
*/
Copy link

ghost commented Mar 24, 2014

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)

@tarungupta19
Copy link

excellante .. works like a charm

@Muardin
Copy link

Muardin commented Sep 21, 2016

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

@CC007
Copy link

CC007 commented Sep 21, 2017

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   |
+------+

@summyer
Copy link

summyer commented Feb 1, 2018

@CC007

I also need this version

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment