如何将逗号分隔的NVARCHAR转换为SQL Server 2005中的表记录？

I have a list of ids separated by comma like:

我有一个由逗号分隔的ID列表，如：

 1,17,25,44,46,67,88

I want to convert them to a table records ( into a temporary table ) like

我想将它们转换为表记录（进入临时表）之类的

#tempTable

number_
--------
1
17
25
44
46
67
88

It is possible with a function, a table-valued one ?

有一个函数，一个表值的函数？

Why I want this ? I want to use for INNER JOIN clause (into stored procedure) with another table(s) like as:

我为什么要这个？我想用于INNER JOIN子句（进入存储过程）与另一个表，如：

SELECT a,b,c FROM T1
INNER JOIN functionNameWhichReturnsTable 
ON functionNameWhichReturnsTable.number_ = T1.a

I cannot use IN because I will use stored procedure which accepts a parameter of type NVARCHAR. That parameter will provide the list of ids.

我不能使用IN，因为我将使用接受NVARCHAR类型参数的存储过程。该参数将提供ID列表。

Thank you

谢谢

4 个解决方案

#1

Possible duplicate of separate comma separated values and store in table in sql server.

可能重复单独的逗号分隔值并存储在sql server中的表中。

Please try a precise one from Comma-Delimited Value to Table:

请尝试从逗号分隔值到表格的精确值：

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END
GO

Check the requirement in other way using XML:

使用XML以其他方式检查需求：

DECLARE @param NVARCHAR(MAX)
SET @param = '1:0,2:1,3:1,4:0'

SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
FROM  
(
    SELECT CAST ('<M>' + REPLACE(@param, ',', '</M><M>') + '</M>' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

#2

The method I found doesn't need a function or XML tricks.

我发现的方法不需要函数或XML技巧。

Basically you transform the string into a single insert statement for the temporary table.
Which then can be used for further processing.

基本上，您将字符串转换为临时表的单个insert语句。然后可以用于进一步处理。

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable;
CREATE TABLE #tempTable (number int);

DECLARE @TEXT varchar(max) = '1,17,25,44,46,67,88';

DECLARE @InsertStatement varchar(max) = 'insert into #tempTable values ('+REPLACE(@TEXT,',','),(')+');';
EXEC (@InsertStatement);

SELECT * FROM #tempTable;

This method is usable for up to 1000 values.
Because 1000 is the max limit of a row value expression.
Or till the limit of the varchar for the @InsertStatement is reached.

此方法最多可用于1000个值。因为1000是行值表达式的最大限制。或者直到达到@InsertStatement的varchar限制。

Also, as Stuart Ainsworth pointed out.
Since this method uses EXEC, be wary of code injection and don't use it for strings based on unverified user input.

此外，正如Stuart Ainsworth指出的那样。由于此方法使用EXEC，因此请注意代码注入，不要将其用于基于未经验证的用户输入的字符串。

#3

Completing the answers, you could also use the CSV string to store multiple values in multiple columns:

完成答案后，您还可以使用CSV字符串在多列中存储多个值：

 --input sql text 
declare @text_IN varchar(max) ='text1, text1.2, text1.3, 1, 2010-01-01\r\n text2, text2.2, text2.3, 2, 2016-01-01'

Split the csv file into rows:

将csv文件拆分为行：

declare @temptable table (csvRow varchar(max))    
declare @DelimiterInit varchar(4) = '\r\n'
declare @Delimiter varchar(1) = '|'
declare @idx int       
declare @slice varchar(max)    

set @text_IN = REPLACE(@text_IN,@DelimiterInit,@Delimiter)


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

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

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

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

Split rows into columns:

将行拆分为列：

;WITH XMLTable (xmlTag)
AS
(
    SELECT CONVERT(XML,'<CSV><champ>' + REPLACE(csvRow,',', '</champ><champ>') + '</champ></CSV>') AS xmlTag
    FROM @temptable
)

 SELECT RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[1]','varchar(max)'))) AS Column1,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[2]','varchar(max)'))) AS Column2,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[3]','varchar(max)'))) AS Column3,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[4]','int'))) AS Column4,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[5]','datetime'))) AS Column5
 FROM XMLTable

#4

The following works:

以下作品：

declare @parStoreNo As varchar(8000) = '1,2,3,4'        
CREATE TABLE #parStoreNo (StoreNo INT)-- drop #parStoreNo
declare @temptable VARCHAR(1000) = @parStoreNo
declare @SQL VARCHAR(1000) 
SELECT @SQL = CONVERT(VARCHAR(1000),' select ' + REPLACE(ISNULL(@temptable,' NULL '),',', ' AS Col UNION ALL SELECT ')) 
INSERT #parStoreNo (StoreNo)
EXEC (@SQL)

#1