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

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

创建一个函数：

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))RETURNS @Results TABLE (Items nvarchar(4000))ASBEGIN    DECLARE @Index INT    DECLARE @Slice nvarchar(4000)    -- HAVE TO SET TO 1 SO IT DOESN鈥橳 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    RETURNEND

将字符串

@str

和定界符（，）传递给函数。

SELECT Items FROM [dbo].[Split] (@str, ',')

它将结果返回为表格：

Items0.000.001576.950.004105.881017.870.006700.70

请参见 SQL Fiddle