MSSQL: Split Function

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE FUNCTION [dbo].[fx_split] ( @String NVARCHAR(MAX), @Delimiter CHAR(1) ) RETURNS @Output TABLE(f_row INT, f_result NVARCHAR(MAX)) BEGIN DECLARE @Start INT, @End INT, @Row INT SELECT @Start = 1, @End = CHARINDEX(@Delimiter, @String), @Row = 1 WHILE @Start < LEN(@String) + 1 BEGIN IF @End = 0 BEGIN SET @End = LEN(@String) + 1 END INSERT INTO @Output (f_row, f_result) VALUES (@Row, SUBSTRING(@String, @Start, @End - @Start)) SET @Start = @End + 1 SET @End = CHARINDEX(@Delimiter, @String, @Start) SET @Row = @Row + 1 END RETURN END