当前位置： 文江博客话题详情

将充满逗号分隔值的 varchar 传递给 SQL Server IN 函数

发布于 2024-07-20 04:21:38 字数 707 浏览 9 评论 0原文

重复
动态 SQL 逗号分隔值查询
带有 Like 和 In 的参数化查询

我有一个 SQL Server 存储过程，其中我想将一个充满逗号分隔值的 varchar 传递给 IN 函数。 例如：

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN (@Ids);

这当然行不通。 我收到错误：

将 varchar 值“1,2,3,5,4,6,7,98,234”转换为 int 数据类型时转换失败。

如何在不构建动态 SQL 的情况下完成此操作（或相对类似的操作）？

原文

Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In

I have a SQL Server Stored Procedure where I would like to pass a varchar full of comma delimited values to an IN function. For example:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN (@Ids);

This does not work of course. I get the error:

Conversion failed when converting the varchar value '1,2,3,5,4,6,7,98,234' to data type int.

How can I accomplish this (or something relatively similar) without resorting to building dynamic SQL?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（30）

一个人的旅程 2024-07-27 04:21:39

我认为一个非常简单的解决方案可能如下：

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';

I think a very simple solution could be following:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';
回复 原文
蓝咒 2024-07-27 04:21:39

我可以建议像这样使用 WITH ：

DECLARE @Delim char(1) = ',';
SET @Ids = @Ids + @Delim;

WITH CTE(i, ls, id) AS (
    SELECT 1, CHARINDEX(@Delim, @Ids, 1), SUBSTRING(@Ids, 1, CHARINDEX(@Delim, @Ids, 1) - 1)
    UNION ALL
    SELECT i + 1, CHARINDEX(@Delim, @Ids, ls + 1), SUBSTRING(@Ids, ls + 1, CHARINDEX(@Delim, @Ids, ls + 1) - CHARINDEX(@Delim, @Ids, ls) - 1)
    FROM CTE
    WHERE  CHARINDEX(@Delim, @Ids, ls + 1) > 1
)
SELECT t.*
FROM yourTable t
    INNER JOIN
    CTE c
    ON t.id = c.id;

I can suggest using WITH like this:

DECLARE @Delim char(1) = ',';
SET @Ids = @Ids + @Delim;

WITH CTE(i, ls, id) AS (
    SELECT 1, CHARINDEX(@Delim, @Ids, 1), SUBSTRING(@Ids, 1, CHARINDEX(@Delim, @Ids, 1) - 1)
    UNION ALL
    SELECT i + 1, CHARINDEX(@Delim, @Ids, ls + 1), SUBSTRING(@Ids, ls + 1, CHARINDEX(@Delim, @Ids, ls + 1) - CHARINDEX(@Delim, @Ids, ls) - 1)
    FROM CTE
    WHERE  CHARINDEX(@Delim, @Ids, ls + 1) > 1
)
SELECT t.*
FROM yourTable t
    INNER JOIN
    CTE c
    ON t.id = c.id;
回复 原文
滴情不沾 2024-07-27 04:21:39

如果不使用动态 SQL，您必须获取输入变量并使用 split 函数将数据放入临时表中，然后加入其中。

Without using dynamic SQL, you have to take the input variable and use a split function to put the data into a temp table and then join to that.

回复 原文
瞳孔里扚悲伤 2024-07-27 04:21:39

这里有很多答案，但补充一下我的两分钱，我认为 STRING_SPLIT 是解决此类问题的一种非常简单的方法：

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN;
(SELECT value FROM STRING_SPLIT(@Ids, ','))

Tons of answers here, but to add my two cents I think STRING_SPLIT is a very simple approach to this sort of problem:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN;
(SELECT value FROM STRING_SPLIT(@Ids, ','))
回复 原文
秋日私语 2024-07-27 04:21:39

谢谢你的功能我使用了它.........................
这是我的例子

**UPDATE [RD].[PurchaseOrderHeader]
SET     [DispatchCycleNumber] ='10'
 WHERE  OrderNumber in(select * FROM XA.fn_SplitOrderIDs(@InvoiceNumberList))**


CREATE FUNCTION [XA].[fn_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
                SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
                IF @OrderID <> ''
                BEGIN
                        INSERT INTO @ParsedList (OrderID) 
                        VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
                END
                SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
                SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END 
    RETURN
END

Thanks, for your function I Used IT........................
This is my EXAMPLE

**UPDATE [RD].[PurchaseOrderHeader]
SET     [DispatchCycleNumber] ='10'
 WHERE  OrderNumber in(select * FROM XA.fn_SplitOrderIDs(@InvoiceNumberList))**


CREATE FUNCTION [XA].[fn_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
                SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
                IF @OrderID <> ''
                BEGIN
                        INSERT INTO @ParsedList (OrderID) 
                        VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
                END
                SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
                SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END 
    RETURN
END
回复 原文
灰色世界里的红玫瑰 2024-07-27 04:21:39

如果您使用 SQL Server 2008 或更高版本，请使用表值参数； 例如：

CREATE PROCEDURE [dbo].[GetAccounts](@accountIds nvarchar)
AS
BEGIN
    SELECT * 
    FROM accountsTable 
    WHERE accountId IN (select * from @accountIds)
END

CREATE TYPE intListTableType AS TABLE (n int NOT NULL)

DECLARE @tvp intListTableType 

-- inserts each id to one row in the tvp table    
INSERT @tvp(n) VALUES (16509),(16685),(46173),(42925),(46167),(5511)

EXEC GetAccounts @tvp

If you use SQL Server 2008 or higher, use table valued parameters; for example:

CREATE PROCEDURE [dbo].[GetAccounts](@accountIds nvarchar)
AS
BEGIN
    SELECT * 
    FROM accountsTable 
    WHERE accountId IN (select * from @accountIds)
END

CREATE TYPE intListTableType AS TABLE (n int NOT NULL)

DECLARE @tvp intListTableType 

-- inserts each id to one row in the tvp table    
INSERT @tvp(n) VALUES (16509),(16685),(46173),(42925),(46167),(5511)

EXEC GetAccounts @tvp
回复 原文
不交电费瞎发啥光 2024-07-27 04:21:39

已经有一段时间了，但我过去曾使用 XML 作为临时措施来完成此操作。

我不能为此承担任何责任，但恐怕我不再知道我从哪里得到这个想法：

-- declare the variables needed
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)

-- The string you want to split
SET @str='A,B,C,D,E,Bert,Ernie,1,2,3,4,5'

-- What you want to split on. Can be a single character or a string
SET @delimiter =','

-- Convert it to an XML document
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

-- Select back from the XML
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

Its been a while but I have done this in the past using XML as a interim.

I can't take any credit for this, but I'm afraid I no longer know where I got this idea from:

-- declare the variables needed
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)

-- The string you want to split
SET @str='A,B,C,D,E,Bert,Ernie,1,2,3,4,5'

-- What you want to split on. Can be a single character or a string
SET @delimiter =','

-- Convert it to an XML document
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

-- Select back from the XML
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
回复 原文
等风来 2024-07-27 04:21:39

创建一个如下所示的表函数，该函数解析逗号分隔的 varchar 并返回一个可以与其他表进行内部联接的表。

CREATE FUNCTION [dbo].[fn_SplitList]
(
  @inString     varchar(MAX)  = '',
  @inDelimiter  char(1)       = ',' -- Keep the delimiter to 100 chars or less.  Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return  table
(
  Unit  varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN 
    INSERT INTO @tbl_Return
    SELECT DISTINCT
      LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
    FROM
    (
      --
      --  Replace any delimiters in the string with the "X" tag.
      --
      SELECT
        CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
      FROM
      (
        --
        --  Convert the string and delimiter into XML.
        --
        SELECT
          (SELECT @inString FOR XML PATH('')) AS prsString,
          (SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
      ) AS s0
    ) AS s1
    CROSS APPLY units.nodes('X') x(piece)
  RETURN
END

===================================================
现在在您的代码中使用上面创建的表函数，函数的创建是数据库中的一次性活动，可以跨数据库使用，也可以在同一服务器上使用。

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT
     *
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
     ON sl.unit = st.tableid

Create a table function like below which parse comma separated varchar and returns a table that can be inner joined with other tables.

CREATE FUNCTION [dbo].[fn_SplitList]
(
  @inString     varchar(MAX)  = '',
  @inDelimiter  char(1)       = ',' -- Keep the delimiter to 100 chars or less.  Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return  table
(
  Unit  varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN 
    INSERT INTO @tbl_Return
    SELECT DISTINCT
      LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
    FROM
    (
      --
      --  Replace any delimiters in the string with the "X" tag.
      --
      SELECT
        CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
      FROM
      (
        --
        --  Convert the string and delimiter into XML.
        --
        SELECT
          (SELECT @inString FOR XML PATH('')) AS prsString,
          (SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
      ) AS s0
    ) AS s1
    CROSS APPLY units.nodes('X') x(piece)
  RETURN
END

=================================================
Now consume above created table function in your code,creation of function is one time activity in your database that can be used across databases as well on same server.

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT
     *
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
     ON sl.unit = st.tableid
回复 原文
豆芽 2024-07-27 04:21:39

我之前已经编写了一个存储过程来演示如何执行此操作。
您基本上必须处理字符串。
我试图在这里发布代码，但格式变得很糟糕。

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE [dbo].[uspSplitTextList]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitTextList
--
-- Description:
--    splits a separated list of text items and returns the text items
--
-- Arguments:
--    @list_text        - list of text items
--    @Delimiter        - delimiter
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
-- 09/18/2006 - WSR : added to this project
--
CREATE PROCEDURE uspSplitTextList
   @list_text           text,
   @Delimiter           varchar(3)
AS

SET NOCOUNT ON

DECLARE @InputLen       integer         -- input text length
DECLARE @TextPos        integer         -- current position within input text
DECLARE @Chunk          varchar(8000)   -- chunk within input text
DECLARE @ChunkPos       integer         -- current position within chunk
DECLARE @DelimPos       integer         -- position of delimiter
DECLARE @ChunkLen       integer         -- chunk length
DECLARE @DelimLen       integer         -- delimiter length
DECLARE @ItemBegPos     integer         -- item starting position in text
DECLARE @ItemOrder      integer         -- item order in list
DECLARE @DelimChar      varchar(1)      -- first character of delimiter (simple delimiter)

-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )

-- process list
IF @list_text IS NOT NULL
   BEGIN

   -- initialize
   SET @InputLen = DATALENGTH(@list_text)
   SET @TextPos = 1
   SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
   SET @DelimLen = DATALENGTH(@Delimiter)
   SET @ItemBegPos = 1
   SET @ItemOrder = 1
   SET @ChunkLen = 1

   -- cycle through input processing chunks
   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
      BEGIN

      -- get current chunk
      SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)

      -- setup initial variable values
      SET @ChunkPos = 1
      SET @ChunkLen = DATALENGTH(@Chunk)
      SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

      -- loop over the chunk, until the last delimiter
      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
         BEGIN

         -- see if this is a full delimiter
         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
            BEGIN

            -- insert position
            INSERT INTO #list_items (item_order, item_begpos, item_endpos)
            VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

            -- adjust positions
            SET @ItemOrder = @ItemOrder + 1
            SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
            SET @ChunkPos = @DelimPos + @DelimLen

            END
         ELSE
            BEGIN

            -- adjust positions
            SET @ChunkPos = @DelimPos + 1

            END

         -- find next delimiter      
         SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

         END

      -- adjust positions
      SET @TextPos = @TextPos + @ChunkLen

      END

   -- handle last item
   IF @ItemBegPos <= @InputLen
      BEGIN

      -- insert position
      INSERT INTO #list_items (item_order, item_begpos, item_endpos)
      VALUES (@ItemOrder, @ItemBegPos, @InputLen)

      END

   -- delete the bad items
   DELETE FROM #list_items
   WHERE item_endpos < item_begpos

   -- return list items
   SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
   FROM #list_items
   ORDER BY item_order

   END

DROP TABLE #list_items

RETURN

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I've written a stored procedure to show how to do this before.
You basically have to process the string.
I tried to post the code here but the formatting got all screwy.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE [dbo].[uspSplitTextList]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitTextList
--
-- Description:
--    splits a separated list of text items and returns the text items
--
-- Arguments:
--    @list_text        - list of text items
--    @Delimiter        - delimiter
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
-- 09/18/2006 - WSR : added to this project
--
CREATE PROCEDURE uspSplitTextList
   @list_text           text,
   @Delimiter           varchar(3)
AS

SET NOCOUNT ON

DECLARE @InputLen       integer         -- input text length
DECLARE @TextPos        integer         -- current position within input text
DECLARE @Chunk          varchar(8000)   -- chunk within input text
DECLARE @ChunkPos       integer         -- current position within chunk
DECLARE @DelimPos       integer         -- position of delimiter
DECLARE @ChunkLen       integer         -- chunk length
DECLARE @DelimLen       integer         -- delimiter length
DECLARE @ItemBegPos     integer         -- item starting position in text
DECLARE @ItemOrder      integer         -- item order in list
DECLARE @DelimChar      varchar(1)      -- first character of delimiter (simple delimiter)

-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )

-- process list
IF @list_text IS NOT NULL
   BEGIN

   -- initialize
   SET @InputLen = DATALENGTH(@list_text)
   SET @TextPos = 1
   SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
   SET @DelimLen = DATALENGTH(@Delimiter)
   SET @ItemBegPos = 1
   SET @ItemOrder = 1
   SET @ChunkLen = 1

   -- cycle through input processing chunks
   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
      BEGIN

      -- get current chunk
      SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)

      -- setup initial variable values
      SET @ChunkPos = 1
      SET @ChunkLen = DATALENGTH(@Chunk)
      SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

      -- loop over the chunk, until the last delimiter
      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
         BEGIN

         -- see if this is a full delimiter
         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
            BEGIN

            -- insert position
            INSERT INTO #list_items (item_order, item_begpos, item_endpos)
            VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

            -- adjust positions
            SET @ItemOrder = @ItemOrder + 1
            SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
            SET @ChunkPos = @DelimPos + @DelimLen

            END
         ELSE
            BEGIN

            -- adjust positions
            SET @ChunkPos = @DelimPos + 1

            END

         -- find next delimiter      
         SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

         END

      -- adjust positions
      SET @TextPos = @TextPos + @ChunkLen

      END

   -- handle last item
   IF @ItemBegPos <= @InputLen
      BEGIN

      -- insert position
      INSERT INTO #list_items (item_order, item_begpos, item_endpos)
      VALUES (@ItemOrder, @ItemBegPos, @InputLen)

      END

   -- delete the bad items
   DELETE FROM #list_items
   WHERE item_endpos < item_begpos

   -- return list items
   SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
   FROM #list_items
   ORDER BY item_order

   END

DROP TABLE #list_items

RETURN

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
回复 原文
紫瑟鸿黎 2024-07-27 04:21:39

我和用户KM有同样的想法。 但不需要额外的表号。 就只有这个功能。

CREATE FUNCTION [dbo].[FN_ListToTable]
(
    @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
   ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN
    DECLARE @number int = 0
    DECLARE @childString varchar(502) = ''
    DECLARE @lengthChildString int = 0
    DECLARE @processString varchar(502) = @SplitOn + @List + @SplitOn

    WHILE @number < LEN(@processString)
    BEGIN
        SET @number = @number + 1
        SET @lengthChildString = CHARINDEX(@SplitOn, @processString, @number + 1) - @number - 1
        IF @lengthChildString > 0
        BEGIN
            SET @childString = LTRIM(RTRIM(SUBSTRING(@processString, @number + 1, @lengthChildString)))

            IF @childString IS NOT NULL AND @childString != ''
            BEGIN
                INSERT INTO @ParsedList(ListValue) VALUES (@childString)
                SET @number = @number + @lengthChildString - 1
            END
        END
    END

RETURN

END

这是测试：

SELECT ListValue FROM dbo.FN_ListToTable('/','a/////bb/c')

结果：

   ListValue
______________________
   a
   bb
   c

I have same idea with user KM. but do not need extra table Number. Just this function only.

CREATE FUNCTION [dbo].[FN_ListToTable]
(
    @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
   ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN
    DECLARE @number int = 0
    DECLARE @childString varchar(502) = ''
    DECLARE @lengthChildString int = 0
    DECLARE @processString varchar(502) = @SplitOn + @List + @SplitOn

    WHILE @number < LEN(@processString)
    BEGIN
        SET @number = @number + 1
        SET @lengthChildString = CHARINDEX(@SplitOn, @processString, @number + 1) - @number - 1
        IF @lengthChildString > 0
        BEGIN
            SET @childString = LTRIM(RTRIM(SUBSTRING(@processString, @number + 1, @lengthChildString)))

            IF @childString IS NOT NULL AND @childString != ''
            BEGIN
                INSERT INTO @ParsedList(ListValue) VALUES (@childString)
                SET @number = @number + @lengthChildString - 1
            END
        END
    END

RETURN

END

And here is the test:

SELECT ListValue FROM dbo.FN_ListToTable('/','a/////bb/c')

Result:

   ListValue
______________________
   a
   bb
   c
回复 原文
落墨 2024-07-27 04:21:39
-- select * from dbo.Split_ID('77,106')  

    ALTER FUNCTION dbo.Split_ID(@String varchar(8000))     
    returns @temptable TABLE (ID varchar(8000))     
    as     
    begin     
        declare @idx int     
        declare @slice varchar(8000)     
        declare @Delimiter char(1)
         set @Delimiter =','

        select @idx = 1     
            if len(@String)<1 or @String is null  return     

        while @idx!= 0     
        begin     
            set @idx = charindex(@Delimiter,@String)     
            if @idx!=0     
                set @slice = left(@String,@idx - 1)     
            else     
                set @slice = @String     

            if(len(@slice)>0)
                insert into @temptable(ID) values(@slice)     

            set @String = right(@String,len(@String) - @idx)     
            if len(@String) = 0 break     
        end 
    return     
    end

-- select * from dbo.Split_ID('77,106')  

    ALTER FUNCTION dbo.Split_ID(@String varchar(8000))     
    returns @temptable TABLE (ID varchar(8000))     
    as     
    begin     
        declare @idx int     
        declare @slice varchar(8000)     
        declare @Delimiter char(1)
         set @Delimiter =','

        select @idx = 1     
            if len(@String)<1 or @String is null  return     

        while @idx!= 0     
        begin     
            set @idx = charindex(@Delimiter,@String)     
            if @idx!=0     
                set @slice = left(@String,@idx - 1)     
            else     
                set @slice = @String     

            if(len(@slice)>0)
                insert into @temptable(ID) values(@slice)     

            set @String = right(@String,len(@String) - @idx)     
            if len(@String) = 0 break     
        end 
    return     
    end
回复 原文
无声静候 2024-07-27 04:21:39

您可以这样做：

create or replace 
PROCEDURE UDP_SETBOOKMARK 
(
  P_USERID IN VARCHAR2  
, P_BOOKMARK IN VARCHAR2  
) AS 
BEGIN

UPDATE T_ER_Bewertung
SET LESEZEICHEN = P_BOOKMARK
WHERE STAMM_ID in( select regexp_substr(P_USERID,'[^,]+', 1, level) from dual
                   connect by regexp_substr(P_USERID, '[^,]+', 1, level) is not null )
and ER_ID = (select max(ER_ID) from T_ER_Bewertung_Kopie);

commit;

END UDP_SETBOOKMARK;

然后尝试使用

Begin
UDP_SETBOOKMARK ('1,2,3,4,5', 'Test');
End;

您也可以在其他情况下将此 IN-Clause 与 regexp_substr 一起使用，只需尝试一下即可。

You could do it like this:

create or replace 
PROCEDURE UDP_SETBOOKMARK 
(
  P_USERID IN VARCHAR2  
, P_BOOKMARK IN VARCHAR2  
) AS 
BEGIN

UPDATE T_ER_Bewertung
SET LESEZEICHEN = P_BOOKMARK
WHERE STAMM_ID in( select regexp_substr(P_USERID,'[^,]+', 1, level) from dual
                   connect by regexp_substr(P_USERID, '[^,]+', 1, level) is not null )
and ER_ID = (select max(ER_ID) from T_ER_Bewertung_Kopie);

commit;

END UDP_SETBOOKMARK;

Then try it with

Begin
UDP_SETBOOKMARK ('1,2,3,4,5', 'Test');
End;

You can use this IN-Clause with regexp_substr in other situations too, just try it.

回复 原文
颜 2024-07-27 04:21:39
Error 493: The column 'i' that was returned from the nodes() method cannot be 
   used directly. It can only be used with one of the four XML data type 
   methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT 
   NULL checks.

使用以下代码片段在 SQL Server 2014 中修复了上述错误

Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT SomeTable.* 
FROM
    SomeTable 
    cross apply @XML.nodes('i') x(i) 
        where SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

Error 493: The column 'i' that was returned from the nodes() method cannot be 
   used directly. It can only be used with one of the four XML data type 
   methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT 
   NULL checks.

The above errorr was fixed in SQL Server 2014 by using following snippet

Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT SomeTable.* 
FROM
    SomeTable 
    cross apply @XML.nodes('i') x(i) 
        where SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')
回复 原文
像极了他 2024-07-27 04:21:39

试试这个：

SELECT ProductId, Name, Tags  
FROM Product  
WHERE '1,2,3,' LIKE '%' + CAST(ProductId AS VARCHAR(20)) + ',%';

正如

Try this:

SELECT ProductId, Name, Tags  
FROM Product  
WHERE '1,2,3,' LIKE '%' + CAST(ProductId AS VARCHAR(20)) + ',%';

As said on the last example of this link

回复 原文
红ご颜醉 2024-07-27 04:21:39

这个简单的答案适用于 SQL 2016 标准

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN (SELECT ss.value from string_split(@Ids, ',') ss)

This simple answer is for SQL 2016 standard

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN (SELECT ss.value from string_split(@Ids, ',') ss)
回复 原文
伊面 2024-07-27 04:21:39

最好又简单的方法。

DECLARE @AccumulateKeywordCopy NVARCHAR(2000),@IDDupCopy NVARCHAR(50);
SET @AccumulateKeywordCopy ='';
SET @IDDupCopy ='';
SET @IDDup = (SELECT CONVERT(VARCHAR(MAX), <columnName>) FROM <tableName> WHERE <clause>)

SET @AccumulateKeywordCopy = ','+@AccumulateKeyword+',';
SET @IDDupCopy = ','+@IDDup +',';
SET @IDDupCheck = CHARINDEX(@IDDupCopy,@AccumulateKeywordCopy)

Best and simple approach.

DECLARE @AccumulateKeywordCopy NVARCHAR(2000),@IDDupCopy NVARCHAR(50);
SET @AccumulateKeywordCopy ='';
SET @IDDupCopy ='';
SET @IDDup = (SELECT CONVERT(VARCHAR(MAX), <columnName>) FROM <tableName> WHERE <clause>)

SET @AccumulateKeywordCopy = ','+@AccumulateKeyword+',';
SET @IDDupCopy = ','+@IDDup +',';
SET @IDDupCheck = CHARINDEX(@IDDupCopy,@AccumulateKeywordCopy)
回复 原文
烟柳画桥 2024-07-27 04:21:39
CREATE TABLE t 
  ( 
     id   INT, 
     col1 VARCHAR(50) 
  ) 

INSERT INTO t 
VALUES     (1, 
            'param1') 

INSERT INTO t 
VALUES     (2, 
            'param2') 

INSERT INTO t 
VALUES     (3, 
            'param3') 

INSERT INTO t 
VALUES     (4, 
            'param4') 

INSERT INTO t 
VALUES     (5, 
            'param5') 

DECLARE @params VARCHAR(100) 

SET @params = ',param1,param2,param3,' 

SELECT * 
FROM   t 
WHERE  Charindex(',' + Cast(col1 AS VARCHAR(8000)) + ',', @params) > 0

工作小提琴在这里找到 Fiddle

CREATE TABLE t 
  ( 
     id   INT, 
     col1 VARCHAR(50) 
  ) 

INSERT INTO t 
VALUES     (1, 
            'param1') 

INSERT INTO t 
VALUES     (2, 
            'param2') 

INSERT INTO t 
VALUES     (3, 
            'param3') 

INSERT INTO t 
VALUES     (4, 
            'param4') 

INSERT INTO t 
VALUES     (5, 
            'param5') 

DECLARE @params VARCHAR(100) 

SET @params = ',param1,param2,param3,' 

SELECT * 
FROM   t 
WHERE  Charindex(',' + Cast(col1 AS VARCHAR(8000)) + ',', @params) > 0

working fiddle find here Fiddle

回复 原文
流星番茄 2024-07-27 04:21:39

我遇到了同样的问题，并且我不想在源数据库上留下任何痕迹 - 即没有存储过程或函数。 我是这样处理的：

declare @IDs table (Value int)

insert into @IDs values(1)
insert into @IDs values(2)
insert into @IDs values(3)
insert into @IDs values(5)
insert into @IDs values(4)
insert into @IDs values(6)
insert into @IDs values(7)
insert into @IDs values(98)
insert into @IDs values(234)


SELECT * 
FROM sometable 
WHERE tableid IN (select Value from @IDs)

I ran into the same issue, and I don't want to have any footprint on the source database - i.e. no stored procedures or functions. I went about it this way:

declare @IDs table (Value int)

insert into @IDs values(1)
insert into @IDs values(2)
insert into @IDs values(3)
insert into @IDs values(5)
insert into @IDs values(4)
insert into @IDs values(6)
insert into @IDs values(7)
insert into @IDs values(98)
insert into @IDs values(234)


SELECT * 
FROM sometable 
WHERE tableid IN (select Value from @IDs)
回复 原文
ま柒月 2024-07-27 04:21:39

@RBarryYoung（上面）的答案对我有用。
但是，当逗号分隔的字符串值之间有空格时，它将省略带空格的 ID。 所以我删除了空格。

看看下面的代码片段。

Declare @Ids varchar(50) Set @Ids = '1   ,   2,3'
set @Ids=','+Replace(@Ids,' ', '')+',';

Select * from [tblEmployee]
where Charindex(','+cast(ID as varchar(8000))+',', @Ids) > 0

The answer by @RBarryYoung (above) worked for me.
But when you have spaces in between the comma separated string values, then it will omit IDs with spaces. So I removed the spaces.

Take a look at the code snippet below.

Declare @Ids varchar(50) Set @Ids = '1   ,   2,3'
set @Ids=','+Replace(@Ids,' ', '')+',';

Select * from [tblEmployee]
where Charindex(','+cast(ID as varchar(8000))+',', @Ids) > 0
回复 原文
暗恋未遂 2024-07-27 04:21:39
WHERE someId IN (SELECT convert(int, value) FROM string_split(@stringOfCommaDelimitedIds, ','))

WHERE someId IN (SELECT convert(int, value) FROM string_split(@stringOfCommaDelimitedIds, ','))
回复 原文
瑶笙 2024-07-27 04:21:39

正如上面马特所说的那样，它如下所示（修正了一点）并且它正在工作：

 DECLARE @XML XML
 Declare @Ids VARCHAR(MAX)='1001,1002,1003,1004'
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
--SELECT @XML

SELECT x.i.value('.', 'VARCHAR(MAX)') Code,FOOD_PRODUCT FROM DIET_CAT_FOOD_ITEMS_MST 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  DIET_CAT_FOOD_ITEMS_MST.CODE = x.i.value('.', 'VARCHAR(MAX)')

As told by Matt above done it like below(Rectified little bit) and it's working:

 DECLARE @XML XML
 Declare @Ids VARCHAR(MAX)='1001,1002,1003,1004'
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
--SELECT @XML

SELECT x.i.value('.', 'VARCHAR(MAX)') Code,FOOD_PRODUCT FROM DIET_CAT_FOOD_ITEMS_MST 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  DIET_CAT_FOOD_ITEMS_MST.CODE = x.i.value('.', 'VARCHAR(MAX)')
回复 原文
国际总奸 2024-07-27 04:21:39
DECLARE @Ids varchar(8000);
SET @Ids = '3,5,4,6';
SELECT convert(int, value) FROM string_split(@Ids, ',')

DECLARE @Ids varchar(8000);
SET @Ids = '3,5,4,6';
SELECT convert(int, value) FROM string_split(@Ids, ',')
回复 原文
寻找一个思念的角度 2024-07-27 04:21:39

我发现最简单的方法是使用 FIND_IN_SET

FIND_IN_SET(column_name, values)

values=(1,2,3)

SELECT name WHERE FIND_IN_SET(id, values)

The simplest way i found was to use FIND_IN_SET

FIND_IN_SET(column_name, values)

values=(1,2,3)

SELECT name WHERE FIND_IN_SET(id, values)
回复 原文
恍梦境° 2024-07-27 04:21:39

这对于我不想使用 CTE 并且也不想使用内部联接的要求之一很有用。

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
    
SELECT
   cn1,cn2,cn3
FROM tableName
WHERE columnName in (select Value from fn_SplitString(@ids, ','))

分割字符串的函数：

CREATE FUNCTION [dbo].[fn_SplitString] ( @stringToSplit VARCHAR(MAX), @seperator Char )  
RETURNS  
 @returnList TABLE ([Value] [nvarchar] (500))  
AS  
BEGIN  
  
 DECLARE @name NVARCHAR(255)  
 DECLARE @pos INT  
  
 WHILE CHARINDEX(@seperator, @stringToSplit) > 0  
 BEGIN  
  SELECT @pos  = CHARINDEX(@seperator, @stringToSplit)    
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)  
  
  INSERT INTO @returnList   
  SELECT @name  
  
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)  
 END  
  
 INSERT INTO @returnList  
 SELECT @stringToSplit  
  
 RETURN  
END

This came in handy for one of my requirements where I did not want to use CTE and also did not want to go with the inner join.

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
    
SELECT
   cn1,cn2,cn3
FROM tableName
WHERE columnName in (select Value from fn_SplitString(@ids, ','))

Function for split string :

CREATE FUNCTION [dbo].[fn_SplitString] ( @stringToSplit VARCHAR(MAX), @seperator Char )  
RETURNS  
 @returnList TABLE ([Value] [nvarchar] (500))  
AS  
BEGIN  
  
 DECLARE @name NVARCHAR(255)  
 DECLARE @pos INT  
  
 WHILE CHARINDEX(@seperator, @stringToSplit) > 0  
 BEGIN  
  SELECT @pos  = CHARINDEX(@seperator, @stringToSplit)    
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)  
  
  INSERT INTO @returnList   
  SELECT @name  
  
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)  
 END  
  
 INSERT INTO @returnList  
 SELECT @stringToSplit  
  
 RETURN  
END
回复 原文
弥枳 2024-07-27 04:21:38

当然，如果你像我一样懒，你可以这样做：

Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'

Select * from sometable
 where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0

Of course if you're lazy like me, you could just do this:

Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'

Select * from sometable
 where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0
回复 原文
往事随风而去 2024-07-27 04:21:38

不要使用循环分割字符串的函数！，我下面的函数将非常快地分割字符串，无需循环！

在使用我的函数之前，您需要设置一个“辅助”表，每个数据库只需执行一次：

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

使用此函数来分割字符串，它不会循环并且非常快：

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

您可以使用此函数作为联接中的表：

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

这是您的示例：

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)
回复 原文
木格 2024-07-27 04:21:38

无表无函数无循环 基于

我们的 DBA 建议使用 XML 将列表解析为表的想法。

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT * 
FROM
    SomeTable 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

这些似乎与 @KM 的答案具有相同的性能，但我认为简单得多。

No Table No Function No Loop

Building on the idea of parsing your list into a table our DBA suggested using XML.

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT * 
FROM
    SomeTable 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

These seems to have the same performance as @KM's answer but, I think, a lot simpler.

回复 原文
毁我热情 2024-07-27 04:21:38

您可以创建一个返回表的函数。

所以你的陈述将类似于“

select * from someable 
 join Splitfunction(@ids) as splits on sometable.id = splits.id

这是一个模拟函数”。

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            IF @OrderID <> ''
            BEGIN
                INSERT INTO @ParsedList (OrderID) 
                VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
            END
            SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
            SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END 
    RETURN
END

You can create a function that returns a table.

so your statement would be something like

select * from someable 
 join Splitfunction(@ids) as splits on sometable.id = splits.id

Here is a simular function.

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            IF @OrderID <> ''
            BEGIN
                INSERT INTO @ParsedList (OrderID) 
                VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
            END
            SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
            SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END 
    RETURN
END
回复 原文
没有你我更好 2024-07-27 04:21:38

这是一个非常常见的问题。 罐头答案，几个不错的技术：

http://www.sommarskog.se/arrays- in-sql-2005.html

It's a very common question. Canned answer, several nice techniques:

http://www.sommarskog.se/arrays-in-sql-2005.html

回复 原文
静待花开 2024-07-27 04:21:38

这非常有效！ 下面的答案太复杂了。 不要将其视为动态的。 按如下方式设置您的商店程序：

(@id as varchar(50))
as

Declare @query as nvarchar(max)
set @query ='
select * from table
where id in('+@id+')'
EXECUTE sp_executesql @query

This works perfectly! The below answers are too complicated. Don't look at this as dynamic. Set up your store procedure as follows:

(@id as varchar(50))
as

Declare @query as nvarchar(max)
set @query ='
select * from table
where id in('+@id+')'
EXECUTE sp_executesql @query
回复 原文
~没有更多了~

关于作者

旧城空念

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

╭⌒浅淡时光〆

文章 0 评论 0

慕巷

文章 0 评论 0

浅生活

文章 0 评论 0

bal

文章 0 评论 0

lqwuliang

文章 0 评论 0

后来的我们

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文