T-SQL Comma delimited value from resultset to in clause in Subquery

Question

I have an issue where in my data I will have a record returned where a column value will look like

-- query

Select col1 from myTable where id = 23

-- result of col1 111, 104, 34, 45

I want to feed these values to an in clause. So far I have tried:

-- Query 2 -- try 1

Select * from mytableTwo
where myfield in (
SELECT col1
from myTable where id = 23)

-- Query 2 -- try 2

Select * from mytableTwo
where myfield in (
SELECT '''' +  
Replace(col1, ',', ''',''') + ''''
from myTable where id = 23)

-- query 2 test -- This works and will return data, so I verify here that data exists

Select * from mytableTwo
where myfield in ('111', '104', '34', '45')

Why aren't query 2 try 1 or 2 working?

Maybe better to write a table-valued function that turns the comma-delimited list into a table, and then you can join against it. That said, this is ugly stuff for a relational database. — Tim, Jul 08 '13 at 17:40
The IN(...) syntax is converted `at compile-time` to OR. it is "syntactic sugar". You'd need to use dynamic SQL approach to achieve your desired result using the IN(..) syntax. — Tim, Jul 08 '13 at 17:43
One option is to normalize myTable so you have one value per row. Then Query 2 -- try 1 would work. — paparazzo, Jul 08 '13 at 17:55
Many suggestions [here](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause "Parameterizing an SQL IN clause?"), as well as in many other similar questions, if you'd only want to look for them. However, consider changing the schema and storing your value list as a column (in a separate table) rather than as a single value (suggestions for that are aplenty on SO as well). — Andriy M, Jul 08 '13 at 19:09

score 1 · Accepted Answer · answered Jul 08 '13 at 17:42

You don't want an in clause. You want to use like:

select *
from myTableTwo t2
where exists (select 1
              from myTable t
              where id = 23 and
                    ', '+t.col1+', ' like '%, '+t2.myfield+', %' 
             );

This uses like for the comparison in the list. It uses a subquery for the value. You could also phrase this as a join by doing:

select t2.*
from myTableTwo t2 join
     myTable t
     on t.id = 23 and
        ', '+t.col1+', ' like '%, '+t2.myfield+', %';

However, this could multiply the number of rows in the output if there is more than one row with id = 23 in myTable.

score 0 · Answer 2 · answered Jul 08 '13 at 17:49

If you observe closely, Query 2 -- try 1 & Query 2 -- try 2 are considered as single value.

like this :

WHERE myfield in ('111, 104, 34, 45')

which is not same as :

WHERE myfield in ('111', '104', '34', '45')

So, If you intend to filter myTable rows from MyTableTwo, you need to extract the values of fields column data to a table variable/table valued function and filter the data.

I have created a table valued function which takes comma seperated string and returns a table value. you can refer here T-SQL : Comma separated values to table

Final code to filter the data :

DECLARE @filteredIds VARCHAR(100)

-- Get the filter data
SELECT @filteredIds = col1
FROM myTable WHERE id = 23

-- TODO : Get the script for [dbo].[GetDelimitedStringToTable] 
-- from the given link and execute before this
SELECT * 
FROM mytableTwo T
CROSS APPLY [dbo].[GetDelimitedStringToTable] ( @filteredIds, ',') F
WHERE T.myfield = F.Value

Please let me know If this helps you!

score 0 · Answer 3 · answered Jul 08 '13 at 17:51

I suppose col is a character type, whose result would be like like '111, 104, 34, 45'. If this is your situation, it's not the best of the world (denormalized database), but you can still relate these tables by using character operators like LIKE or CHARINDEX. The only gotcha is to convert the numeric column to character -- the default conversion between character and numeric is numeric and it will cause a conversion error.

Since @Gordon, responded using LIKE, I present a solution using CHARINDEX:

SELECT *
FROM mytableTwo tb2
WHERE EXISTS (
    SELECT 'x'
    FROM myTable tb1
    WHERE tb1.id = 23
    AND CHARINDEX(CONVERT(VARCHAR(20), tb2.myfield), tb1.col1) > 0
)

T-SQL Comma delimited value from resultset to in clause in Subquery

3 Answers3