Question Re: Splitting a comma separated string issue in Technology Q&A

Splitting a comma separated string issue

Former Member — Tue, 10 Jul 2012 19:18:18 GMT

Hi I am using Sybase 10 and am passing a comma separated string into a stored procedure to split into multiple rows. At the moment I am only able to get this working when I pass numbers in. Anytime I pass in a word or letter I get a message saying that column doesn't exist?

ALTER PROCEDURE "DBA"."sp_split_data"(@testData varchar(500))
begin
declare @sql varchar(8000);
declare local temporary table TempTable(
testRecord varchar(500) null,
) on commit delete rows;

set @sql='insert into TempTable select '
+replace(@testData,',',' union all select ');  
execute immediate @sql;

select testRecord
from TempTable

end

I call this stored procedure by: call sp_split_new_onet('1,2,3') and it will return the following:

testrecord

But anytime I would execute the stored procedure call sp_split_new_onet('A,B,C') it would say the columns do not exist.

So basically I am trying to find a way to split up a comma separated string where words/characters are passed in. Any help is appreciated.

Thanks!

Re: Splitting a comma separated string issue

Former Member — Tue, 10 Jul 2012 19:34:13 GMT

SQL Anywhere 10 should already have a mechanism built in for this purpose.

Have you looked at the sa_split_list procedure?

sa_split_list system procedure

With your two examples you can execute:

CALL sa_split_list('1,2,3');

or

CALL sa_split_list('A,B,C');

This procedure also allows for different delimiters, so you could split the list on semi-colons, spaces, etc.

Also, there are two columns presented by the stored procedure, but you could match your desired output by using a select list with an alias.

I.e.

SELECT row_value as testRecord FROM sa_split_list('A,B,C');

On a separate note, the sp_split_new_onet('A,B,C') is failing because it is looking for columns named A, B, and C; when executing sp_split_new_onet('1,2,3'), it succeeds because 1, 2, and 3 are being parsed as literals instead of column names.

Hope This Helps!

Since sa_split_list was introduced in version 10, you might need to resort to something else in earlier versions, something like the following may work (untested however):

CREATE PROCEDURE sp_split_list(str LONG VARCHAR, delim LONG VARCHAR)
RESULT(record LONG VARCHAR)
BEGIN
    DECLARE last_pos INTEGER;
    DECLARE next_pos INTEGER;

    IF (str = '') THEN
        SELECT '';
        RETURN;
    END IF;

    SET next_pos = LOCATE(str,delim);
    SET last_pos = 1;

    CREATE TABLE #temp (
        record LONG VARCHAR
    );

    WHILE (next_pos > 0) LOOP
        INSERT #temp VALUES(SUBSTR(str,last_pos,next_pos-last_pos));
        SET last_pos = next_pos+LENGTH(delim);
        SET next_pos = LOCATE(str,delim,last_pos);
    END LOOP;
    INSERT #temp VALUES(SUBSTR(str,last_pos));

    SELECT * FROM #temp;
END;

CALL sp_split_list('my,string,to,split',',');

This approach steers away from REPLACE and EXECUTE IMMEDIATE, helping to avoid SQL injection or other potential pitfalls.

Re: Splitting a comma separated string issue

Former Member — Fri, 13 Jul 2012 17:42:52 GMT

Thanks that sa_split_list is a very handy tool. I did just find out that some of the earlier Sybase versions we use(8) do not have this built in feature. Anything you would suggest for that or a way to modify my original sp_split_new_onet to allow the split of 'A,B,C'?

Re: Splitting a comma separated string issue

Former Member — Fri, 13 Jul 2012 18:37:51 GMT

You could come up with something in SQL, I've edited my answer with a possible example.