question

iWilma-0816 avatar image
0 Votes"
iWilma-0816 asked MelissaMa-msft commented

SQL Loop through string with Commas?

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

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

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @iWilma-0816,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

iWilma-0816 avatar image
0 Votes"
iWilma-0816 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

iWilma-0816 avatar image
0 Votes"
iWilma-0816 answered

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 :)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @iWilma-0816,

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

iWilma-0816 avatar image
0 Votes"
iWilma-0816 answered YitzhakKhabinsky-0887 commented

Thank you much, this resolves the issue.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@iWilma-0816,

Glad to hear that the proposed solution is working for you.
Please don't forget to mark it as Answer.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @iWilma-0816,

Welcome to Microsoft Q&A!

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Please also refer below method and check whether it is also helpful to you:

Create one function [dbo].[SplitString], refer to this forum.

 CREATE FUNCTION [dbo].[SplitString]
  (
      @List NVARCHAR(MAX),
      @Delim VARCHAR(255)
  )
  RETURNS TABLE
  AS
      RETURN ( SELECT [Value] FROM 
        ( 
          SELECT 
            [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
            CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
          FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
            FROM sys.all_objects) AS x
            WHERE Number <= LEN(@List)
            AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
        ) AS y
      );

Then call this function as below:

 DECLARE @Str VARCHAR(100);
 SET @Str = '22,45,50,105,' 
    
 SELECT * FROM Table1 WHERE ID in (select * from dbo.SplitString(@Str, ','))

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.