CODEWITHSUNDEEP
sqlsql-server-2012
Jason Renaldo
Jason Renaldo

Reputation: 2822

Selecting multiple ints in query from a varchar

Consider the following table:

DECLARE @tmp_Example 
TABLE (
    TestInt int,
    TestName varchar(max)
  );

INSERT INTO @tmp_Example (TestInt, TestName)
VALUES (22,'Bob'),
(23,'James'),
(24,'Joe');

SELECT * from @tmp_Example
WHERE TestInt = 23;

I am getting into situations where somebody might want to select multiple results for TestInt, and I am receiving it like so:

('23,24')

Without changing the schema of the table, how can I support such a query? I fiddled with splitting it like so:

DECLARE @testEx as varchar(max) = '23,24';

SELECT * from @tmp_Example
WHERE TestInt = CAST(dbo.Split(@testEx,',') AS int);

Upvotes: 0

Views: 114

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Assuming dbo.Split is what I think it is, a table-valued function that will return integers 23 and 24 in their own rows:

SELECT t.TestInt, t.TestName
FROM @tmp_Example AS t
INNER JOIN dbo.Split(@testEx, ',') AS s
ON t.TestInt = s.OutputColumnName;

SQLfiddle demo

This approach works also (assuming you handle the case where someone has spaces in their list, e.g. 23, 24):

SELECT TestInt, TestName
FROM @tmp_Example
WHERE ','+REPLACE(@testEx, ' ', '')+',' 
  LIKE '%,'+cast(TestInt as varchar(255))+',%';

SQLfiddle demo

Upvotes: 5

Related Questions