SQL Split Function for string

This SQL Split Function is use to SPLIT a sentences based on the Delimeter. Delimeter is a string character used to identify substring limits.

--Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='Paro|Haa|Thimphu'
SET @Delimiter = '|'
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Output:
Paro
Haa
Thimphu

Source: sqltutorials.blogspot.com