我正在使用sql Server,我已成功将表的行转换为逗号分隔值,现在我想将该逗号分隔值的字符串转换回表的行.
我有这个字符串(Varchar)
DECLARE @str AS varchar(Max) SET @str = '0.00,0.00,1576.95,4105.88,1017.87,6700.70'
我希望这些值成行.
喜欢
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和分隔符(,)传递给函数.
SELECT Items FROM [dbo].[Split] (@str,',')
它会将结果作为表返回：
Items 0.00 0.00 1576.95 0.00 4105.88 1017.87 0.00 6700.70
