Blog

Split large string in SQL Server

Posted by Pankaj Kumar Blogs | SQL Server 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
COMMENT USING
PREMIUM SPONSORS
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.
Employers - Post Free Jobs