SQL SQL中将字符串列表转换为整数列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16663789/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting String List into Int List in SQL
提问by JulyOrdinary
I have a nvarchar(MAX) in my stored procedure which contains the list of int values, I did it like this as it is not possible to pass int list to my stored procedure, but, now I am getting problem as my datatype is int and I want to compare the list of string. Is there a way around by which I can do the same?
我的存储过程中有一个 nvarchar(MAX)，它包含 int 值列表，我这样做是因为不可能将 int 列表传递给我的存储过程，但是，现在我遇到了问题，因为我的数据类型是 int我想比较字符串列表。有没有办法让我做同样的事情？
---myquerry----where status in (@statuslist)
but the statuslist contains now string values not int, so how to convert them into INT?
但是状态列表现在包含字符串值而不是 int，那么如何将它们转换为 INT？
UPDate:
更新：
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//
)
AS SET FMTONLY OFF; DECLARE @sql nvarchar(MAX), @paramlist nvarchar(MAX)
作为 FMTONLY 关闭；声明 @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
So when I implement that function that splits then I am not able to specify the charcter or delimiter as it is not accepting it as (@List,',').
因此，当我实现拆分的函数时，我无法指定字符或分隔符，因为它不接受它为(@List,',')。
or (','+@List+',').
或(','+@List+',')。
回答by Nick N.
It is possible to send an int list to your stored procedure using XML parameters. This way you don't have to tackle this problem anymore and it is a better and more clean solution.
可以使用 XML 参数将 int 列表发送到您的存储过程。这样你就不必再解决这个问题，它是一个更好、更干净的解决方案。
have a look at this question: Passing an array of parameters to a stored procedure
看看这个问题： 将参数数组传递给存储过程
or check this code project: http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty
或检查此代码项目：http: //www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty
However if you insist on doing it your way you could use this function:
但是，如果您坚持按照自己的方式进行操作，则可以使用此功能：
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
Call it like this:
像这样调用它：
SELECT *
FROM Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))
回答by Azade
You can work with string list too. I always do.
您也可以使用字符串列表。我经常做。
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)
回答by RePierre
Actually, you cansend the list of intvalues to your procedure by creating a User Defined Table Type. However, this implies more work in order to populate the table parameter.
其实，你可以的列表发送int通过创建一个值到你的过程User Defined Table Type。但是，这意味着需要做更多的工作来填充表参数。
In your case, you can use the sp_executesqlstored procedure to achieve what you want like this:
在您的情况下，您可以使用sp_executesql存储过程来实现您想要的效果：
declare @statement nvarchar(4000) = '----your query---- where status in ('
+ @statusList +')'
sp_executesql @statement
回答by K D
You can do this by using sql function which will return you an integer array.. It would be great if you pass @Delimiter separated string to your stored procedure which could be processed properly afterwards.
您可以通过使用 sql 函数来执行此操作，该函数将返回一个整数数组。如果您将 @Delimiter 分隔的字符串传递给您的存储过程，然后可以正确处理，那就太好了。
Write one function to split the data as following
编写一个函数来拆分数据，如下所示
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
And then use it like following, i am using (,) as @Delimiter here
然后像下面一样使用它，我在这里使用 (,) 作为 @Delimiter
SELECT ResultValue [YourSchema].[SplitValues](@statuslist,',')
回答by WiiMaxx
here is an example of how to do it and the Linkfor more informations
这是一个如何做的例子和更多信息的链接
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 ############################
回答by Jhollman
This works for me on an Informix DataBase:
这在 Informix 数据库上对我有用：
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'));
