[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

SQL Query Question on IN

I have built a list of integers as a string.  What I am trying to do is use this created list in the 'IN' function in a SQL Query.
0
PenningtonCounty
Asked:
PenningtonCounty
1 Solution
 
Ephraim WangoyaCommented:
CAN YOU SHOW YOUR QUERY
0
 
almanderCommented:
Assuming that your list is in a variable named @ListOfIntegers and is delimited by a ,

The attached function will return each of your integers in the form of a table.

It would be used like this.

WHERE FIELD_NAME IN (SELECT value FROM fn_Split(@ListOfIntegers, ','))
CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')

RETURNS @Strings TABLE

(    

  position int IDENTITY PRIMARY KEY,

  value varchar(8000)   

)

AS

BEGIN

 

DECLARE @index int 

SET @index = -1 

 

WHILE (LEN(@text) > 0) 

  BEGIN  

    SET @index = CHARINDEX(@delimiter , @text)  

    IF (@index = 0) AND (LEN(@text) > 0)  

      BEGIN   

        INSERT INTO @Strings VALUES (ltrim(rtrim(@text)))

          BREAK  

      END  

    IF (@index > 1)  

      BEGIN   

        INSERT INTO @Strings VALUES (ltrim(rtrim(LEFT(@text, @index - 1)))) 

        SET @text = RIGHT(@text, (LEN(@text) - @index))  

      END  

    ELSE 

      SET @text = RIGHT(@text, (LEN(@text) - @index)) 

    END

  RETURN

END

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now