Monday, December 27, 2010

Split Strings in MySQL

MySql does not include a built in function to split a comma seperated string. But it is importantat to have such a function in some cases.

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

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

Follwing 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 seperated 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 slpited 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. Helpful article...thanks....

    ReplyDelete
  2. 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
  3. 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
  4. useful article.. thanks..

    ReplyDelete
  5. 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