单独的逗号分隔值并存储在 sql server 的表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14811316/
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
separate comma separated values and store in table in sql server
提问by prabu R
I am having a stored procedure which gets the comma separated value as an input. I need to separate it and needs to store it in a table as individual rows.
我有一个存储过程，它获取逗号分隔值作为输入。我需要将它分开并需要将它作为单独的行存储在表中。
Let the input for SP is :
让 SP 的输入为：
Rule_ID ListType_ID Values
1 2 319,400,521,8465,2013
I need to store it in a table called DistributionRule_x_ListTypein the below format:
我需要将它存储在一个名为DistributionRule_x_ListType以下格式的表中：
Rule_ID ListType_ID Value
1 2 319
1 2 400
1 2 521
1 2 8465
1 2 2013
My SP looks like below:
我的 SP 如下所示：
ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(@Rule_ID int,
@ListType_ID int,
@Values VARCHAR(MAX)=NULL
)
AS
BEGIN
INSERT INTO DistributionRule_x_ListType (Rule_ID,ListType_ID,Value)
VALUES (@Rule_ID,@ListType_ID,@Values)
END
回答by Taryn
You will need to create a split function similar to this:
您将需要创建一个类似于此的拆分函数：
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
Then in your stored procedure, you will call the function to split your string:
然后在您的存储过程中，您将调用该函数来拆分您的字符串：
ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(
@Rule_ID int,
@ListType_ID int,
@Values VARCHAR(MAX)=NULL
)
AS
BEGIN
INSERT INTO DistributionRule_x_ListType (Rule_ID, ListType_ID, Value)
SELECT @Rule_ID, @ListType_ID, items
FROM [dbo].[Split] (@Values, ',') -- call the split function
END
When you execute the stored procedure, it will split the values and insert the multiple rows into your table:
当您执行存储过程时，它将拆分值并将多行插入到您的表中：
exec spInsertDistributionRuleListType 1, 2, '319,400,521,8465,2013';
See SQL Fiddle with Demo. This will insert the following result:
请参阅SQL Fiddle with Demo。这将插入以下结果：
| RULE_ID | LISTTYPE_ID | VALUE |
---------------------------------
| 1 | 1 | 10 |
| 1 | 2 | 319 |
| 1 | 2 | 400 |
| 1 | 2 | 521 |
| 1 | 2 | 8465 |
| 1 | 2 | 2013 |
回答by Prashant16
you can do it with charindex like
你可以用 charindex 来做
DECLARE @id VARCHAR(MAX)
SET @id = @Values --'319,400,521,8465,2013,'
WHILE CHARINDEX(',', @id) > 0
BEGIN
DECLARE @tmpstr VARCHAR(50)
SET @tmpstr = SUBSTRING(@id, 1, ( CHARINDEX(',', @id) - 1 ))
INSERT INTO DistributionRule_x_ListType
( Rule_ID ,
ListType_ID ,
Value
)
VALUES ( @Rule_ID ,
@ListType_ID ,
@tmpstr)
)
SET @id = SUBSTRING(@id, CHARINDEX(',', @id) + 1, LEN(@id))
END
回答by Sarath Avanavu
You can do this without dbo.Splitfunction.
您可以在没有dbo.Split功能的情况下执行此操作。
Here is your sample data
这是您的示例数据
SELECT * INTO #TEMP
FROM
(
SELECT 1 Rule_ID, 2 ListType_ID, '319,400,521,8465,2013' [Values]
UNION ALL
SELECT 1 , 3 , '100,200'
)TAB
Now execute the following query and will select all comma separated values for each Rule_IDand ListType_ID.
现在执行以下查询，并将为每个Rule_ID和 选择所有逗号分隔值ListType_ID。
SELECT [Rule_ID],ListType_ID,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values'
FROM
(
SELECT [Rule_ID],ListType_ID,
CAST ('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS Data
FROM #TEMP
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
回答by Sai Kumar Reddy
select Rule_ID ,ListType_ID,Values as value
FROM table
CROSS APPLY STRING_SPLIT(Values, ',');
回答by Candous
Completing @bluefeet answer, you could also use the CSV string to store multiple values in multiple columns:
完成@bluefeet 回答后，您还可以使用 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 hsan
You could create a udf that returns a table var to split the string. We have used the following successfully on MSSQL2005.
您可以创建一个返回表 var 的 udf 来拆分字符串。我们已经在 MSSQL2005 上成功使用了以下内容。
CREATE FUNCTION [dbo].[fn_explode] (
@str_separator NVARCHAR(255),
@str_string VARCHAR(4000)
)
RETURNS @ret_string_parts TABLE (str_value varchar(4000))
AS
BEGIN
DECLARE @intPos INT
DECLARE @intLengthString INT
DECLARE @intTempPatIndex INT
DECLARE @intLengthSeparator INT
SET @str_string = @str_string + @str_separator
SET @intPos = 0
SET @intLengthString = LEN(@str_string)
SET @intLengthSeparator = LEN(@str_separator)
IF PATINDEX ( '%' + @str_separator + '%' , @str_string ) <= 0 BEGIN
INSERT INTO @ret_string_parts
SELECT @str_string
RETURN
END
IF @str_separator = @str_string BEGIN
INSERT INTO @ret_string_parts
SELECT @str_string
RETURN
END
WHILE @intPos <= @intLengthString
BEGIN
SET @intTempPatIndex = PATINDEX('%' + @str_separator + '%', SUBSTRING(@str_string, @intPos,@intLengthString))
IF @intTempPatIndex = 0 BEGIN
INSERT INTO @ret_string_parts
SELECT SUBSTRING(@str_string, @intPos, @intLengthString)
BREAK
END
ELSE BEGIN
IF @intPos = 0 BEGIN
INSERT INTO @ret_string_parts
SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex)
SET @intPos = @intPos + @intTempPatIndex + @intLengthSeparator
END
ELSE BEGIN
INSERT INTO @ret_string_parts
SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex-1)
SET @intPos = @intPos + @intTempPatIndex + (@intLengthSeparator-1)
END
END
END
RETURN
END
