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! |
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. 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
|