Split a string and loop in SQL Server
SQL split string and loop
SQL loop
SQL split
Recently, I needed to insert some data into a simple table, using Microsoft SQL Server and the data was coma delimited.
Solution
Here is a simple how to split a coma delimited string and loop the values:
DECLARE @list varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)
SET @list = 'A101,A203,B12341,C124,'
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 -- for debug porpose
--DO YOUR MAGIC HERE
set @pos = CHARINDEX(',', @list, @pos+@len) +1
END
Note: make sure you add the limiter at the end otherwise the last value from the string will be ignored.