Split a string and loop in SQL Server

declare @list varchar(max)
declare @pos int
declare @len int
declare @value varchar(max)

set @list = 'a1a1a1,b2b2b2b,c3c3c3c,d4d4d4d4,'

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @list, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @list, @pos+1) - @pos
set @value = SUBSTRING(@list, @pos, @len)

print @value 

--Your query

set @pos = CHARINDEX(',', @list, @pos+@len) +1
END