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

  • 1
  • 2
  • 3

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!

asked 10 Jul '12, 19:18

potn1's gravatar image

potn1
31114
accept rate: 0%

edited 11 Jul '12, 06:45

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654


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.

permanent link

answered 10 Jul '12, 19:34

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

edited 13 Jul '12, 18:41

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'?

(13 Jul '12, 17:42) potn1
Replies hidden
2

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

(13 Jul '12, 18:37) Tyson Lewis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×12

question asked: 10 Jul '12, 19:18

question was seen: 11,624 times

last updated: 13 Jul '12, 18:42