Split string by comma in MYSql

In Mysql do not have in split method. We should go for different approach.
There are two steps to split string by comma(‘,’) and save into our database table
1.First Create one Function as `splitString`
2 Secondly Create one Store Procedure as ‘AddNames’
Syntax For Creating Function
CREATE FUNCTION splitString(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 = ‘Mahesh,Ramesh,Rakesh’
take second name as separated by comma. For that function
mysql> select splitString(‘Mahesh,Ramesh,Rakesh’,’,’, 2) as name;
———–
| name |
———–
| Ramesh |
———–
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(‘Mahesh,Ramesh,Rakesh’) – LENGTH(REPLACE(‘Mahesh,Ramesh,Rakesh’, ‘,’, ”)) INTO @noOfCommas;
Query OK
mysql> select @noOfCommas;
——————-
| @noOfCommas |
——————-
| 2 |
——————-

Using above query and splitString function, 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 AddNames( IN fullname varchar(256))
BEGIN
CREATE TABLE IF NOT EXISTS `Names` (`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 fullname IS NULL
THEN
SELECT LENGTH(fullname ) – LENGTH(REPLACE(fullname , ‘,’, ”)) INTO @noOfCommas;
IF @noOfCommas = 0
THEN
INSERT INTO Names(name) VALUES(fullname );
ELSE
SET x = @noOfCommas + 1;
WHILE y <= x DO
SELECT split_string(fullname , ',', y) INTO @flName;
INSERT INTO Names(name) VALUES(@flName);
SET y = y + 1;
END WHILE;
END IF;
END IF;
END

Example of Calling StoredProcedure
mysql> call AddNames(‘Mahesh,Ramesh,Rakesh’);

mysql> select * from Names;
|—-|———–
| ID | NAME |
|—-|———–
| 1 | Mahesh |
| 2 | Ramesh |
| 3 | Rakesh |
|—-|———–

Popular Posts