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

前端之家收集整理的这篇文章主要介绍了使用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循环创建自己的函数,
和每个元素提取基于分隔符位置搜索,
然后将元素插入到临时表中,该函数将返回结果.

解决方法

这可以在不使用动态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'

我希望这有帮助…

原文链接:https://www.f2er.com/mssql/84246.html

猜你在找的MsSQL相关文章