Looping through a delimited string in SQL.


… and before I forget about this one too:

DECLARE @list	VARCHAR(500);
DECLARE @item 	VARCHAR(10);

SET @list = 'abc, 123/tab/ABC/crlf/XYZ,890';
-- /tab/ and /crlf/ represent actual tabs and cr/lf in data.
-- SET @list = REPLACE(REPLACE(@list,'/tab/',CHAR(10)),'/crlf/', CHAR(13) + CHAR(10));

DECLARE @dx        INT;
DECLARE @delimiter CHAR(1);

SELECT @dx = 1
       , @delimiter = ','
       , @list = REPLACE(REPLACE(REPLACE(@list, CHAR(13) + CHAR(10), @delimiter), CHAR(10), @delimiter), CHAR(9), @delimiter) + @delimiter; 

WHILE ( @dx < LEN(@list) ) 
  BEGIN 
      SELECT @item = RTRIM(LTRIM(SUBSTRING(@list, @dx, CHARINDEX(@delimiter, @list, @dx) - @dx)))
             , @dx = CHARINDEX(@delimiter, @list + @delimiter, @dx) + 1; 
      SELECT @item;
  END;

Edit: The method above is still a valid and useful way to loop through entries in a list, but if you want to use the entries as a set in a query, then reference this entry

Advertisements

2 thoughts on “Looping through a delimited string in SQL.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s