SQL SplitString function

/*while set ANSI_NULLS on, select * from table1 where column_name1=NULL will return 0 row, select * from table1 where column_name1<>NULL will return 0 row, no matter column_name1 contains null value or not.*/ /*while set ANSI_NULLS off, select * from table1 where column_name1=NULL will return null rows, select * from table1 where column_name1<>NULL will return not null rows, select * from table1 where column_name1<>value1 will return not equal value1 and not null.*/ SET ANSI_NULLS ON GO /*while set QUOTED_IDENTIFIER on, Identifiers can be delimited by double quotation marks, and text must be delimited by single quotes.*/ /*while set QUOTED_IDENTIFIER off, Identifier is not enclosed in quotes, and must comply with all Transact-SQL rules for identifiers.*/ SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_SplitString] ( @String NVARCHAR(2000), @Delimiter NVARCHAR(1) ) RETURNS @ValueTable TABLE ([Value] NVARCHAR(2000), [Index] INT) BEGIN DECLARE @NextString NVARCHAR(2000) DECLARE @Pos INT DECLARE @NextPos INT DECLARE @Index INT DECLARE @LastChar NVARCHAR(1) SET @String = RTRIM(LTRIM(@String)) SET @NextString = '' SET @Index = 0 SET @LastChar = RIGHT(@String,1) IF (@LastChar <> @Delimiter ) BEGIN SET @String = @String + @Delimiter END SET @Pos = CHARINDEX(@Delimiter, @String) SET @NextPos = 1 WHILE (@Pos <> 0) BEGIN SET @NextString = SUBSTRING(@String, 1, @Pos - 1) SET @NextString = RTRIM(LTRIM(@NextString)) INSERT INTO @ValueTable ( [Value], [Index]) VALUES (@NextString, @Index) SET @String = SUBSTRING(@String, @Pos + 1, LEN(@String)) SET @NextPos = @Pos SET @Pos = CHARINDEX(@Delimiter, @String) SET @Index = @Index + 1 END RETURN END