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
I call this stored procedure by: call sp_split_new_onet('1,2,3') and it will return the following:
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.
SQL Anywhere 10 should already have a mechanism built in for this purpose.
Have you looked at the sa_split_list 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.
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.