MySQL Split String Function « Federico Cargnelutti

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

Written by Federico

February 22, 2009 at 12:11 pm

Posted in Databases

17 Responses

Subscribe to comments with RSS.

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

Federico Cargnelutti