Split a string and loop it’s values in SQL Server Or Foreach in SQL Server

From My wiki
Jump to: navigation, search


DECLARE @items varchar(8000) DECLARE @pos INT DECLARE @len INT DECLARE @value varchar(8000)

SET @items = 'A,A1,A2,A3,'

set @INDX = 0 set @LNT= 0

WHILE CHARINDEX(',', @items, @INDX +1)>0 BEGIN

   set @len = CHARINDEX(',', @items, @INDX +1) - @INDX 
   set @value = SUBSTRING(@items, @INDX , @LNT)
           
   PRINT @value -- for debug porpose   
   
   --DO YOUR MAGIC HERE
   set @INDX = CHARINDEX(',', @items, @INDX +@LNT) +1

END