如何将逗号分隔的 NVARCHAR 转换为 SQL Server 2005 中的表记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15585632/
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
How to convert comma separated NVARCHAR to table records in SQL Server 2005?
提问by Snake Eyes
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 JOINclause (into stored procedure) with another table(s) like as:
为什么我想要这个？我想将 forINNER JOIN子句（进入存储过程）与另一个表一起使用，例如：
SELECT a,b,c FROM T1
INNER JOIN functionNameWhichReturnsTable
ON functionNameWhichReturnsTable.number_ = T1.a
I cannot use INbecause 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
谢谢
回答by TechDo
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)
回答by LukStorms
Here's a trick that doesn't need a function or XML.
这是一个不需要函数或 XML 的技巧。
Basically the string gets transformed into a single insert statement for a temporary table.
基本上，字符串被转换为临时表的单个插入语句。
The temp table can then be used for further processing.
然后可以使用临时表进行进一步处理。
IF OBJECT_ID('tempdb..#tmpNum') IS NOT NULL
DROP TABLE #tmpNum;
CREATE TABLE #tmpNum (num int);
DECLARE @TEXT varchar(max) = '1,17,25,44,46,67,88';
DECLARE @InsertStatement varchar(max);
SET @InsertStatement = 'insert into #tmpNum (num) values ('+REPLACE(@TEXT,',','),(')+');';
EXEC (@InsertStatement);
-- use the temp table
SELECT *
FROM YourTable t
WHERE t.id IN (SELECT DISTINCT num FROM #tmpNum);
This method is usable for up to 1000 values.
Because 1000 is the max limit of a row value expression.
此方法最多可用于 1000 个值。
因为 1000 是行值表达式的最大限制。
Also, as Stuart Ainsworth pointed out.
Since this method uses Dynamic Sql, be wary of code injection and don't use it for strings based on user input.
此外，正如斯图尔特·安斯沃思所指出的那样。
由于此方法使用动态Sql，因此请注意代码注入，不要将其用于基于用户输入的字符串。
Side-note
边注
Starting from MS Sql Server 2016, one could simply use the STRING_SPLITfunction.
从 MS Sql Server 2016 开始，可以简单地使用STRING_SPLIT函数。
DECLARE @TEXT varchar(max);
SET @TEXT = '1,17,25,44,46,67,88';
SELECT t.*
FROM YourTable t
JOIN (SELECT DISTINCT CAST(value AS INT) num FROM STRING_SPLIT(@TEXT, ',')) nums
ON t.id = nums.num;
回答by Candous
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
回答by Petrus Norlin
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)
回答by Dev5413
I am using XML Function as below...
我正在使用 XML 函数如下...
DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'
Declare @x XML
select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)
select t.value('.', 'int') as inVal
from @x.nodes('/A') as x(t)
I prefer this because not need to create any separate function and proc. Also I don't have to opt dynamic SQL query which I prefer most. Convert Comma Separated String to Table
我更喜欢这个，因为不需要创建任何单独的函数和过程。此外，我不必选择我最喜欢的动态 SQL 查询。 将逗号分隔的字符串转换为表格
回答by user3107451
DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832'
DECLARE @x XML
select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)
select t.value('.', 'int') as inVal
from @x.nodes('/A') as x(t)
回答by Akshay Mishra
Try this code
试试这个代码
SELECT RTRIM(part) as part
INTO Table_Name
FROM dbo.splitstring(@Your_Comma_string,',')
splitstring Function is as follows
splitstring 函数如下
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
