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

48 responses to “MySQL Split String Function”

  1. 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.

  3. Have I read “worth nothing” ???
    That’s a great function, very useful !!
    Thanks for sharing Fédérico ! :-)

  4. 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.

  5. 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

  14. 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?

  18. 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

  19. 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.

  23. 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.

  24. 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?

  25. 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??

  35. 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..

  36. Great function, solved a problem on multiple inserts from delimited string. Thanks very much for posting.

  37. 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.

  48. Thank you very nice :) first run create function , second query working.

Leave a comment