SQL：使用IN子句将字符串转换为IDS [英] SQL:Casting a String to IDS with IN clause

问题描述

DECLARE @STR_IDS VARCHAR(15)
SET @STR_IDS='7,15,18'
UPDATE TBL_USERS WHERE ID IN  @STR_IDS

我知道更新语句不会工作，因为ID是INT类型，并且我正在替换varachar值。

I know the update statement would not work as the ID is of type INT and i am replacing a varachar value there .How can i change the query so that it will be executed like this in effect ?

 UPDATE TBL_USERS WHERE ID IN (7,15,18)

感谢advace

推荐答案

Op不提及数据库，所以我只使用SQL Server，因为问题中的示例SQL看起来像TSQL。有很多方法来拆分SQL Server中的字符串。本文涵盖几乎每种方法的PRO和CON：

Op doesn't mention database, so I'll just use SQL Server, because the example SQL in the question looks like TSQL. There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

在SQL Server 2005及以后的数组和列表，当表值参数不剪切Erland Sommarskog

您需要创建一个拆分函数。这是如何使用拆分函数：

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢使用数字表方法在TSQL中分割字符串，但是有很多方法可以在SQL Server中拆分字符串，请参见前面的链接，它解释了每个的PRO和CON。

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

为了数字表方法的工作，你需要执行此一次性表设置，将创建一个包含从1到10,000的行的表 Numbers ：

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

此拆分函数：

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 TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    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!=''

);
GO 

现在，您可以轻松地将CSV字符串拆分为表格并加入使用它，然而你需要，即使从动态sql。以下是如何从您的问题使用它：

You can now easily split a CSV string into a table and join on it or use it however you need, even from within dynamic sql. Here is how to use it from your question:

UPDATE t
    SET Col1=...
    FROM dbo.FN_ListToTable(',','7,15,18') dt
        INNER JOIN TBL_USERS                t ON  CAST(dt.value AS INT)=t.id

这篇关于SQL：使用IN子句将字符串转换为IDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！