使用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'

我但愿这有辅佐…

（编辑：湖南网）

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