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:
In order to get the surname out of the full name:
SELECT SPLIT_STR(full_name, " ", 2) from persons