user3441151 user3441151 - 6 months ago 12
SQL Question

MySQL String seperation by comma operator

I have String

asdasdwdfef,rgrgtggt,weef
and i want output like in table format as shown below

id decription
1 asdasdwdfef
2 rgrgtggt
3 weef


For this i created a procedure
here is my procedure

DELIMITER ;;
CREATE Procedure Split(_RowData text, _Delimeter text)
BEGIN
DECLARE _Iterator INT default 1;
DECLARE _FoundIndex INT;
DECLARE _Data varchar(255);
SET _FoundIndex = LOCATE(_Delimeter,_RowData);
DROP TABLE IF EXISTS _RtnValue;
CREATE temporary TABLE _RtnValue(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID));
WHILE _FoundIndex > 1 DO
INSERT INTO _RtnValue (description)
SELECT
_Data = LTRIM(RTRIM(SUBSTRING(_RowData, 1, _FoundIndex - 1)));
set _RowData = SUBSTRING(_RowData, _FoundIndex + LENGTH(_Delimeter) / 2, LENGTH(_RowData));
SET _Iterator = _Iterator + 1;
SET _FoundIndex = LOCATE(_Delimeter, _RowData);
END WHILE;
INSERT INTO _RtnValue(description) SELECT _Data = LTRIM(RTRIM(_RowData));
select * from _RtnValue;
END


But when i execute it by using following command

call Split('asdasdwdfef,rgrgtggt,weef', ',');


it gives me the following output:

id decription
1 NULL
2 NULL
3 NULL


Please let me know how to fix this issue.
I am using MySQL.

Answer

I got the answer

First create new function

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, '');

Then create stored procedure

DELIMITER ;;
CREATE PROCEDURE Split(in fullstr varchar(255))
BEGIN
    DECLARE a INT Default 0 ;
    DECLARE str VARCHAR(255);

    DROP TABLE IF EXISTS my_temp_table;
    CREATE temporary TABLE my_temp_table(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID));

    simple_loop: LOOP
        SET a=a+1;
        SET str=SPLIT_STR(fullstr,",",a);
        IF str='' THEN
            LEAVE simple_loop;
        END IF;
        #Do Inserts into temp table here with str going into the row
        insert into my_temp_table (description) values (str);
   END LOOP simple_loop;
   select * from my_temp_table;
END

After that when i call it by call Split('asas,d,sddf,dfd'); it gives me the output that what i want.

Thanx for every suggestion.