Split Long string separated by comma in Sql Server

Introduction:

 In this article I am going to explain how to split a long string (including commas)into individual strings (according to commas)in SQL server.

Use the following query to do this job:

      DECLARE @Splitted_strings NVARCHAR(4000)

      DECLARE @Pos INT

      DECLARE @NextPos INT

      DECLARE @Str NVARCHAR(4000)

      DECLARE @Delimiter NVARCHAR(1)

      SET @Str=’ Split,Long,string,separated,by,comma’

      SET @Delimiter = ','

      SET @Str = @Str + @Delimiter

      SET @Pos = charindex(@Delimiter,@Str)

      WHILE (@pos <> 0)

      BEGIN

      SET @Splitted_strings = substring(@Str,1,@Pos - 1)

      SELECT @Splitted_string -- Show Results

      SET @Str = substring(@Str,@pos+1,len(@Str))

      SET @pos = charindex(@Delimiter,@Str)

      print @Splitted_strings  --print Results

      END

Result will be as:

Split

Long

string

separated

by

comma