Monday, December 27, 2010

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

5 comments:

  1. the above procedure is not executing..,
    Error Code: 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE x INT DEFAULT 0;
    DECLARE y INT DEFAULT 0;
    SET y = 1;

    I' at line 7

    ReplyDelete
  2. I had the same problem ...
    Seems that you're using Squirrel Client for executing your procedure.
    I solved that problem by installing the MySQL-Plugin for Squirrel.
    That sould work for you, too.

    ReplyDelete
  3. useful article.. thanks..

    ReplyDelete
  4. i want to split string with space character and store that in an array so that i can compare each element of array with another string

    ReplyDelete

Note: Only a member of this blog may post a comment.