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:

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, '');

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
Advertisements

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