参见英文答案 >
How do I split a string so I can access item x?41个
UPDATE:
Someone marked this question as duplicate of
07001.
But it’s different,my question is about Sybase sql Anywhere,the other is about MS sql Server. These are two different sql engines,even if they have the same origin,they have different Syntax. So it’s not duplicate. I wrote in the first place in description and tags that it’s all about Sybase sql Anywhere.
我有字段id_list =’1234,23,56,576,1231,567,122,87876,57553,1216′
我想用它来搜索这个字段：
SELECT * FROM table1 WHERE id IN (id_list)
> id是整数
> id_list是varchar / text
但是这样做不起作用,所以我需要以某种方式将id_list拆分成select查询.
我应该在这里使用什么解决方案我正在使用T-sql Sybase ASA 9数据库(sql Anywhere).
我看到这个,就是用while循环创建自己的函数,
和每个元素提取基于分隔符位置搜索,
然后将元素插入到临时表中,该函数将返回结果.
解决方法
这可以在不使用动态sql的情况下完成,但您需要创建一些支持对象.第一个对象是一个表值函数,它将解析您的字符串并返回一个整数表.第二个对象是一个存储过程,它将具有一个参数,您可以在其中传递字符串(id_list),将其解析为表,然后最终将其连接到您的查询.
首先,创建解析字符串的函数：
CREATE FUNCTION [dbo].[String_To_Int_Table]
(
@list NVARCHAR(1024),@delimiter NCHAR(1) = ',' --Defaults to CSV
)
RETURNS
@tableList TABLE(
value INT
)
AS
BEGIN
DECLARE @value NVARCHAR(11)
DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ','
SET @position = CHARINDEX(@delimiter,@list,1)
IF REPLACE(@list,@delimiter,'') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = LTRIM(RTRIM(LEFT(@list,@position - 1)));
INSERT INTO @tableList (value)
VALUES (cast(@value as int));
SET @list = RIGHT(@list,LEN(@list) - @position);
SET @position = CHARINDEX(@delimiter,1);
END
END
RETURN
END
现在创建存储过程：
CREATE PROCEDURE ParseListExample
@id_list as nvarchar(1024)
AS
BEGIN
SET NOCOUNT ON;
--create a temp table to hold the list of ids
CREATE TABLE #idTable (ID INT);
-- use the table valued function to parse the ids into a table.
INSERT INTO #idTable(ID)
SELECT Value FROM dbo.String_to_int_table(@id_list,',');
-- join the temp table of ids to the table you want to query...
SELECT T1.*
FROM table1 T1
JOIN #idTable T2
on T1.ID = T2.ID
执行示例：
exec ParseListExample @id_list='1234,1216'
我希望这有帮助…
