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

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)

Thanks in advace

回答1:

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:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by 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

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.

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)

Once the Numbers table is set up, create this split function:

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 

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

回答2:

Stolen from here:

CREATE  FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

The just do:

UPDATE  tbl_users
SET     ...
FROM    fn_split('7,15,18', ',') q
JOIN    tbl_users
ON      id = CAST(q.value AS INT)

回答3:

for mysql its really easy just use FIND_IN_SET function

UPDATE TBL_USERS WHERE FIND_IN_SET ( id , '7,15,18')

回答4:

Lately I prefer to use User-Defined Table Types to pass lists of parameters, but I used to use this utility function:

CREATE FUNCTION [dbo].[fn_CommasToIntTable]
(
    @CommaList varchar(8000)
)
RETURNS  @ParsedList TABLE
(
    TokenID int
)
AS
BEGIN
    DECLARE @CurrentToken varchar(10)
    DECLARE @Pos int
    SET @CommaList = LTRIM(RTRIM(@CommaList))+ ','
    SET @Pos = CHARINDEX(',', @CommaList, 1)
    IF REPLACE(@CommaList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @CurrentToken = LTRIM(RTRIM(LEFT(@CommaList, @Pos - 1)))
            IF @CurrentToken <> ''
            BEGIN
                INSERT @ParsedList (TokenID) 
                VALUES (CAST(@CurrentToken AS int))
            END
            SET @CommaList = RIGHT(@CommaList, LEN(@CommaList) - @Pos)
            SET @Pos = CHARINDEX(',', @CommaList, 1)
        END
    END

    RETURN
END

You'd use it like this:

SELECT (Columns)
FROM (Table)
WHERE ID IN (SELECT TokenID FROM dbo.fn_CommasToIntTable(@idList))