Using fn_Split in SQL query

Question

I've been struggling with getting fn_Split to work properly in my query. I've searched high and low and found answers here that seem close to what I need but I'm still unable to make it work.

Basically I'm trying to return records that match entries in an array. I've already got a Table-valued Function working with my database like so:

Select * From dbo.fn_Split('county1, county2',',')

My problem is getting it to work in my application where I want it to return records that have either the same exact counties selected or have the selected counties as part of a larger set of counties (say all of them). The counties are stored in a column called County in the following format: county1, county2, county3 and so on. I now realize that this is part of the problem and it is bad form to have multiple values in one column but I can't do anything about that now unfortunately.

So, here's what I've got so far:

SELECT * FROM MyTable WHERE County IN (SELECT County From dbo.fn_Split(@counties, ','))

This returns all the records for some reason. @counties is in the following format:

county1, county2

Any help would be greatly appreciated. I'm new to writing SQL so forgive me if I didn't explain this well. Thanks!

Answer 1

Use

SELECT *
FROM   MyTable
WHERE  County IN (SELECT value /*<-- Guessed column name*/
                  FROM   dbo.fn_Split(@counties, ',')) 

County can't be the name of the column actually returned from your split function so it is resolved from the outer query.

i.e.

SELECT *
FROM   MyTable
WHERE  County IN (SELECT County )

is the same as

SELECT *
FROM   MyTable
WHERE  County = County 

And will always return all rows from the outer query with non NULL values for County as long as the split function returns at least one row.