Split function in MySQL

MySQL does not provide a built-in split function as explode() is used in PHP. We can, though, define an UDF (User-Defined Function) for it and use it throughout all our database schemas.
User-defined functions are compiled as object files and then added to and removed from the server dynamically using the CREATE FUNCTION and DROP FUNCTION statements.
More information about UDFs in Mysql in http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html.

Let’s call this function SPLIT_STR which will read three mandatory arguments:

  • x VARCHAR(255): The input string
  • delim VARCHAR(12): The boundary string (separator)
  • pos INT: position of the splitted string to be returned by the function

And following is the SQL code to create the function which is stored in the MySQL’s information_schema:

  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And here’s and example of using the SPLIT_STR function. Say you have a table named “persons” with a “full_name” field storing values in the form:

Name1 Surname1
Name2 Surname2
Name3 Surname3

In order to get the surname out of the full name:

SELECT SPLIT_STR(full_name, " ", 2) from persons

6 thoughts on “Split function in MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s