逗号分隔的字符串到SQL Server中的表的列中 [英] Comma-separated String into Table's Column in SQL Server
本文介绍了逗号分隔的字符串到SQL Server中的表的列中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用SQL Server,我已经成功地将表的行转换为逗号分隔的值,现在我想将逗号分隔的值的字符串转换回表的行。
I am working with SQL Server, I have successfully converted a table's rows into comma-separated values, now I want to convert that string of comma-separated values back to table's rows.
我有这个字符串( Varchar
)
DECLARE @str AS varchar(Max)
SET @str = '0.00,0.00,1576.95,0.00,4105.88,1017.87,0.00,6700.70'
我想将这些值转换成行。
I want these values into rows.
喜欢
0.00
0.00
1576
...
推荐答案
创建函数:
CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @Index INT
DECLARE @Slice nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESN’T EQUAL ZERO FIRST TIME IN LOOP
SELECT @Index = 1
WHILE @Index !=0
BEGIN
SELECT @Index = CHARINDEX(@Delimiter,@String) --Getting the indexof the first Occurrence of the delimiter
-- Saving everything to the left of the delimiter to the variable SLICE
IF @Index !=0
SELECT @Slice = LEFT(@String,@Index - 1)
ELSE
SELECT @Slice = @String
-- Inserting the value of Slice into the Results SET
INSERT INTO @Results(Items) VALUES(@Slice)
--Remove the Slice value from Main String
SELECT @String = RIGHT(@String,LEN(@String) - @Index)
-- Break if Main String is empty
IF LEN(@String) = 0 BREAK
END
RETURN
END
传递字符串 @str
和分隔符(,)。
Pass the string @str
and the delimiter (,) to the function.
SELECT Items FROM [dbo].[Split] (@str, ',')
作为表:
Items
0.00
0.00
1576.95
0.00
4105.88
1017.87
0.00
6700.70
请参阅 SQL Fiddle
这篇关于逗号分隔的字符串到SQL Server中的表的列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文