May 202016

ADM

Split a string and loop in SQL Server

  • 20 May 2016
  • ADM

 

 

 

Split a string and loop in SQL Server - images/logos/mssql.jpg

 

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.