Equivalent of CHARINDEX OR IN for ints

Question

I want the equivalent of this for ints.

--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0

So something like this.

--Invalid SQL
DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
select * FROM Person AS p
WHERE p.ixPerson IN(@users

Answer 1

I'd look at a numbers table type solution to split that delimited data into something you can work with http://stackoverflow.com/questions/8956671/how-to-convert-varchar-to-integer-in-sql-server-stored-procedure/8958460#8958460 Then you can use the IN clause just fine

Answer 2

I don't love this solution, but this is what I did.

DECLARE @users AS nvarchar(MAX)
SET @users = ('8,10,28')

DECLARE @finalCommand AS NVARCHAR(MAX)
SET @finalCommand = 'SELECT p.sFullName, *
                 FROM   Person as p
                 WHERE  p.ixPerson IN (' +  @users + ')'

EXEC sp_executesql  @finalCommand 

Answer 3

Option 1 (first part ripped directly from Adam Machanic's Blog)

DECLARE @number_of_numbers INT = 100000;
;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS
(
SELECT TOP(@number_of_numbers)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM f
)
SELECT [your],[required],[columns],[here]
FROM Person where Person.ixPerson in (select number from numbers);

Of course, this is just one way to create a numbers table. If you're looking for a random assortment of ids, you can populate a numbers table real quick by using a table variable or something. But then you'd have to separate the list in your code or with a UDF as billinkc proposed at which point in time it might just be better to construct the IN clause.

Option 2 (using the answer by @CadeRoux here):

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  );

declare @nums nvarchar(100);
set @nums = N'1,2,5,10,87';
SELECT [your],[required],[columns],[here]
    FROM Person where Person.ixPerson in (select CAST(s AS INTEGER) s from dbo.Split(N',',@nums));

Answer 4

You haven't really indicated how this is called. Do you really have a string full of comma separated ints or is that just an artifact of some other aspect of your system?

If the outer part of the environment around this requirement is changeable, then typically in SQL Server 2008 and up, I pass a table-valued parameter containing a list of IDs (perhaps from ADO.NET or whatever) and then just use it as a table inside the procedure - i.e. WHERE value IN (SELECT id FROM @table) or INNER JOIN @table AS t ON t.id = users.t

Answer 5

I expect this simple solution would work fine in many cases although it doesn't particularly scale (but neither does the example in your question using CHARINDEX):

DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
SELECT *
FROM Person AS p
WHERE ',' + @users + ',' LIKE '%,' + CAST(VARCHAR(10), p.ixPerson) + ',%'

Answer 6

DECLARE @IDs AS nvarchar(50);
SET @IDs = N'1,2,8,23';

SELECT p.*
FROM dbo.Person AS p
WHERE CHARINDEX(N',' + CONVERT(nvarchar(11), p.ID) + N',', N',' + @IDs + N',') > 0;