Blog

Split large string in SQL Server

By Pankaj Kumar Aug 19 2013
How to split large string separated by comma in SQL Server.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 1.9k
  • 0
'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