Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
Neither example is valid syntax... – Jon Seigel Aug 8 '12 at 15:12
I did not think I had to put a Select * From table to show my point. I have updated the question with valid SQL. – joe Aug 9 '12 at 17:03
Pure t-sql or are you using any front-end code? – swasheck Aug 9 '12 at 17:22
up vote 4 down vote accepted

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 = users.t

share|improve this answer

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

DECLARE @number_of_numbers INT = 100000;
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)
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))
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      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));
share|improve this answer

I'd look at a numbers table type solution to split that delimited data into something you can work with Then you can use the IN clause just fine

share|improve this answer

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'
FROM Person AS p
WHERE ',' + @users + ',' LIKE '%,' + CAST(VARCHAR(10), p.ixPerson) + ',%'
share|improve this answer

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

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

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

EXEC sp_executesql  @finalCommand 
share|improve this answer
DECLARE @IDs AS nvarchar(50);
SET @IDs = N'1,2,8,23';

FROM dbo.Person AS p
WHERE CHARINDEX(N',' + CONVERT(nvarchar(11), p.ID) + N',', N',' + @IDs + N',') > 0;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.