The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.9k310457668


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:

×19
×12

question asked: 10 Jul '12, 19:18

question was seen: 11,980 times

last updated: 13 Jul '12, 18:42