就像 Mikael Eriksson 所说的那样, 有两个非常好的解决方案,一个是使用sa_split_list
系统过程的解决方案,第二是使用CAST
语句的解决方案。
由于sa_split_list
不存在Sybase sql Anywhere 9系统过程,因此我进行了sa_split_list
系统过程替换(我使用了来自 bsivel 答案的部分代码):
CREATE PROCEDURE str_split_list
(in str long varchar, in delim char(10) default ',')
RESULT(
line_num integer,
row_value long varchar)
BEGIN
DECLARE str2 long varchar;
DECLARE position integer;
CREATE TABLE #str_split_list (
line_num integer DEFAULT AUTOINCREMENT,
row_value long varchar null,
primary key(line_num));
SET str = TRIM(str) || delim;
SET position = CHARINDEX(delim, str);
separaterows:
WHILE position > 0 loop
SET str2 = TRIM(LEFT(str, position - 1));
INSERT INTO #str_split_list (row_value)
VALUES (str2);
SET str = RIGHT(str, LENGTH(str) - position);
SET position = CHARINDEX(delim, str);
end loop separaterows;
select * from #str_split_list order by line_num asc;
END
select * from
str_split_list(‘1234,23,56,576,1231,567,122,87876,57553,1216’)
或使用可以更改的指定定界符:
select * from
str_split_list(‘1234,23,56,576,1231,567,122,87876,57553,1216’, ‘,’)