Split Strings in MySQL

MySQL does not have a built in function to split a comma separated string. But it is important to have such  function in some cases.

Think there is a string that contains several names separated by commas and you want to insert each of them separately into a table.

You can write your own MySQL function to do this.

Syntax for create a function is as follows,

CREATE FUNCTION function_name ([parameterlist]) RETURNS datatype

BEGIN

     RETURN value_to_return;

END

Following function takes three arguments and it returns the pointed element in given string.

CREATE FUNCTION split_string(


stringToSplit (VARCHAR(256), sign VARCHAR(12), position INT) RETURNS VARCHAR(256)
BEGIN
        RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position), 
LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position -1)) + 1), sign, '');
END

Example
Given String = 'Dinusha,Nuwan,Nirosh'
Need to take second name separated by comma. For that, function call will look likes follows,

mysql> select split_string('Dinusha,Nuwan,Nirosh',',', 2) as name;

+-----------+

| name      |

+-----------+

| Nuwan     |

+-----------+

This query can be used to get how many comma signs are in the input string

SELECT LENGTH(stringToSplit) - LENGTH(REPLACE(stringToSplit, ',', '')) INTO noOfCommas;

Example

mysql> SELECT LENGTH('Dinusha,Nuwan,Nirosh') - LENGTH(REPLACE('Dinusha,Nuwan,Nirosh', ',', '')) INTO @noOfCommas;

Query OK, 1 row affected (0.00 sec)



mysql> select @noOfCommas;

+--------------------+

| @noOfCommas        |

+--------------------+

|           2        |

+--------------------+

Using above query and split_string function, we can write a stored procedure that do some SQL operations on each element of given string.

Following stored procedure will split the string and insert the spitted strings into a table.

CREATE PROCEDURE insertEngineer( IN engineerName varchar(256))
BEGIN
    CREATE TABLE IF NOT EXISTS `engineer` (`ID` int(11) NOT NULL auto_increment,`NAME` varchar(256) NOT NULL,
PRIMARY KEY (`ID`)) AUTO_INCREMENT=1 ;
 
    DECLARE x INT DEFAULT 0; 
    DECLARE y INT DEFAULT 0; 
    SET y = 1;  
 
    IF NOT engineerName IS NULL 
    THEN 
           SELECT LENGTH(engineerName) - LENGTH(REPLACE(engineerName, ',', '')) INTO @noOfCommas; 
      
           IF  @noOfCommas = 0 
          THEN 
                 INSERT INTO engineer(NAME) VALUES(engineerName); 
          ELSE 
                SET x = @noOfCommas + 1; 
                WHILE y  <=  x DO 
                   SELECT split_string(engineerName, ',', y) INTO @engName; 
                   INSERT INTO engineer(NAME) VALUES(@engName); 
                   SET  y = y + 1; 
                END WHILE; 
        END IF; 
    END IF; 
END

Example of using stored procedure

mysql> call insertEngineer('dinusha,nuwan,nirosh');

Query OK, 1 row affected (0.12 sec)



mysql> select * from engineer;

+----+----------+

| ID | NAME     |

+----+----------+

|  1 | dinusha  |

|  2 | nuwan    |

|  3 | nirosh   |

+----+----------+