Scroll To Top
Blog

Split large string in SQL Server

By Pankaj Kumar Aug 19, 2013
How to split large string separated by comma in SQL Server.
'Hyderabad,Delhi,Kolkata,Mumbai,Chennai'(split one by one)
---------------------------------------------------------------------------------------

DECLARE @Splitted_strings NVARCHAR(4000)
      DECLARE @Pos INT
      DECLARE @NextPos INT
      DECLARE @Str NVARCHAR(4000)
      DECLARE @Delimiter NVARCHAR(1)

      SET @Str ='Hyderabad,Delhi,Kolkata,Mumbai,Chennai'
      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_strings
      SET @Str = substring(@Str,@pos+1,len(@Str))
      SET @pos = charindex(@Delimiter,@Str)
      print @Splitted_strings
      END

Output:
Hyderabad
Delhi
Kolkata
Mumbai
Chennai