使用Sybase SQL Anywhere拆分/爆炸逗号分隔的字符串

参见英文答案 > 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循环创建自己的函数,
和每个元素提取基于分隔符位置搜索,
然后将元素插入到临时表中,该函数将返回结果.

解决方法

首先,创建解析字符串的函数：

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'

我希望这有帮助…

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!