SQL中字符串列表到整型列表的转换

Question

我有一个nvarchar(MAX)在我的存储过程中包含int值的列表，我这样做，因为不可能将int列表传递给我的存储过程，但是，现在我遇到了问题，因为我的数据类型是int，我想比较字符串的列表。有什么方法可以让我做同样的事吗?

---myquerry----where status in (@statuslist)

但是statuslist现在包含字符串值而不是int，那么如何将它们转换为int ?

更新:

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP]
(
@FromDate datetime = 0,
@ToDate datetime = 0,
@ID int=0,
@List nvarchar(MAX) //This is the List which has string ids//
)

设置fmtonly off;声明sql nvarchar(MAX)@paramlist nvarchar (MAX)

SET @sql    = 'SELECT ------ and Code in(@xList)
  and -------------'
SELECT @paramlist = '@xFromDate datetime,@xToDate datetime,@xId int,@xList nvarchar(MAX)'
EXEC sp_executesql @sql, @paramlist, 
@xFromDate = @FromDate ,@xToDate=@ToDate,@xId=@ID,@xList=@List
PRINT @sql

因此，当我实现分割函数时，我无法指定字符或分隔符，因为它不接受它为(@List，'，')

或 (', ' + @ list + ', ')。

Answer 1

可以使用XML参数将int列表发送到存储过程。这样你就不用再处理这个问题了，这是一个更好更干净的解决方案。

看看这个问题:将参数数组传递给存储过程

或检查此代码项目:http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

但是，如果你坚持按你的方式做，你可以使用这个函数:

CREATE FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int
    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)
    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END
    RETURN
END

这样写:

SELECT      *
FROM        Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))

Answer 2

您也可以使用字符串列表。我总是这样。

declare @statuslist nvarchar(max)
set @statuslist = '1, 2, 3, 4'
declare @sql nvarchar(max)
set @sql = 'select * from table where Status in (' + @statuslist + ')'
Execute(@sql)

Answer 3

您可以通过使用sql函数来实现这一点，该函数将返回一个整数数组。如果您将@分隔符分隔的字符串传递给存储过程，则可以在之后正确处理，这将是非常好的。

写一个函数来拆分数据，如下所示

CREATE FUNCTION [dbo].[SplitValues] (@StringArray NVARCHAR(MAX), @Delimiter NVARCHAR(10)) 
RETURNS @ResultedValues table 
(
ResultValue INT
) 
AS 
BEGIN       
  DECLARE @Tokens TABLE(Token nvarchar)         
  DECLARE   @String nvarchar
  WHILE (CHARINDEX(@Delimiter,@StringArray)>0)
   BEGIN 
    INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(SUBSTRING(@StringArray,1,CHARINDEX(@Delimiter,@StringArray)-1))))
    SET @String = SUBSTRING(@StringArray,
    CHARINDEX(@Delimiter,@StringArray)+LEN(@Delimiter),LEN(@StringArray))
   END 
INSERT INTO @ResultedValues (ResultValue ) VALUES ( CAST(LTRIM(RTRIM(@String)) AS INT))
RETURN
END

然后像下面这样使用，我在这里使用(，)作为@Delimiter

SELECT ResultValue [YourSchema].[SplitValues](@statuslist,',')

Answer 4

实际上，您可以通过创建User Defined Table Type将int值列表发送到您的过程。但是，这意味着要填充table参数需要做更多的工作。

在您的情况下，您可以使用sp_executesql存储过程来实现您想要的:

declare @statement nvarchar(4000) = '----your query---- where status in (' 
+ @statusList +')'
sp_executesql @statement

Answer 5

这里有一个如何做的例子和链接以获取更多信息

ALTER FUNCTION iter_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                       number  int NOT NULL) AS

BEGIN
    DECLARE @startpos int,
            @endpos   int,
            @textpos  int,
            @chunklen smallint,
            @str      nvarchar(4000),
            @tmpstr   nvarchar(4000),
            @leftover nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''

    WHILE @textpos <= datalength(@list) / 2
    BEGIN

        SET @chunklen = 4000 - datalength(@leftover) / 2 

        SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
        SET @textpos = @textpos + @chunklen
        SET @startpos = 0
        SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr)
        WHILE @endpos > 0
        BEGIN
            SET @str = substring(@tmpstr, @startpos + 1, @endpos - @startpos - 1) 
            IF @str <> ''
                INSERT @tbl (number) VALUES(convert(int, @str))
            SET @startpos = @endpos
            SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr, @startpos + 1)
        END
        SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
    END
    IF ltrim(rtrim(@leftover)) <> ''
        INSERT @tbl (number) VALUES(convert(int, @leftover))
    RETURN
END

-- ############################ Example ############################
--CREATE    PROCEDURE get_product_names_iter @ids varchar(50) AS
--SELECT    P.ProductName, P.ProductID
--FROM      Northwind..Products P
--JOIN      iter_intlist_to_tbl(@ids) i ON P.ProductID = i.number
--go
--EXEC get_product_names_iter '9 12 27 37'
-- ############################ WICHTIG ############################

Answer 6

这在Informix DataBase上适用:

DROP FUNCTION rrhh:fnc_StringList_To_Table;
CREATE FUNCTION rrhh:fnc_StringList_To_Table (pStringList varchar(250))
    RETURNING INT as NUMERO;
    /* A esta Funcion le podes pasar una cadena CSV con una lista de numeros
     *      Ejem:  EXECUTE FUNCTION fnc_StringList_To_Table('1,2,3,4');
     * y te devolvera una Tabla con dichos numeros separados uno x fila
     * Autor:  Jhollman Chacon @Cutcsa - 2019 */
    DEFINE _STRING VARCHAR(255);
    DEFINE _LEN INT;
    DEFINE _POS INT;
    DEFINE _START INT;
    DEFINE _CHAR VARCHAR(1);
    DEFINE _VAL INT;
    LET _STRING = REPLACE(pStringList, ' ', '');
    LET _START = 0;
    LET _POS = 0;
    LET _LEN = LENGTH(_STRING);
    FOR _POS = _START TO _LEN
        LET _CHAR = SUBSTRING(pStringList FROM _POS FOR 1);
        IF _CHAR <> ',' THEN 
            LET _VAL = _CHAR::INT;
        ELSE 
            LET _VAL = NULL;
        END IF;
        IF _VAL IS NOT NULL THEN 
            RETURN _VAL WITH RESUME;
        END IF;
    END FOR;
END FUNCTION;
EXECUTE FUNCTION fnc_StringList_To_Table('1,2,3,4');
SELECT * FROM TABLE (fnc_StringList_To_Table('1,2,3,4'));