逗号分隔的字符串到SQL Server中的表的列

我正在使用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

见SQL Fiddle

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!