逗号分隔的字符串到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

（编辑：湖南网）

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