SQL Loop through string with Commas?

Question

How do I loop through a VARCHAR(100) and remove the commas? Each ID should be used uniquely in the WHERE Clause.

Example:
DECLARE @Steinar VARCHAR(100);
SET @Steinar = "22,45,50,105,"

I need to use each unique ID in my WHERE Clause.

SELECT * FROM Table1 WHERE ID = @Steinar <---- How do I loop through @Steinar remove the comma and use each ID uniquely?

Answer 1

If you are using SQL Server 2016 or higher, then you can use the STRING_SPLIT (Transact-SQL) function for a set based solution, no need to loop =>

DECLARE @Str VARCHAR(100);  
SET @Str = '22,45,50,105'  
  
SELECT *  
FROM STRING_SPLIT(@Str, ',') AS result  

Answer 2

Thank you much, this resolves the issue.

Answer 3

Hi @iWilma ,

Please try the following solution.
It will work in SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Code INT, City VARCHAR(50));  
INSERT INTO @tbl (Code, City) VALUES  
(10, 'Miami'),  
(45, 'Orlando'),  
(50, 'Dallas'),  
(70, 'Houston');  
-- DDL and sample data population, end  
  
DECLARE @Str VARCHAR(100) = '22,45,50,105,'  
	, @separator CHAR(1) = ',';  
  
DECLARE @parameter XML = TRY_CAST('<root><r>' +   
		REPLACE(@Str, @separator, '</r><r>') +   
		'</r></root>' AS XML);  
  
;WITH rs AS  
(  
   SELECT c.value('.', 'INT') AS Code  
   FROM @parameter.nodes('/root/r/text()') AS t(c)  
)  
SELECT *   
FROM @tbl AS t INNER JOIN   
   rs ON t.Code = rs.Code;  

Answer 4

Hi Olaf Helper

Thank you for your help - in SQL 2014 I am using LTRIM and RTRIM with REPLACE to remove the comma and SPLIT the string item.

Thanks much :)

Answer 5

I am using SQL 2012 and STRING_SPLIT is an invalid object. Is there a different term to use for my version of SQL?