2

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!

Satwik Nadkarny
  • 5,086
  • 2
  • 23
  • 41
user3738691
  • 45
  • 1
  • 1
  • 5
  • BTW If you are on SQL Server 2008 or later you could look into table valued parameters rather than splitting a CSV into tabular form. – Martin Smith Jun 13 '14 at 19:00

1 Answers1

4

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I changed it to SELECT value and it is now returning records that match individual counties. Progress! Now I need to be able to return individual matches as well as if the record has that county as part of a larger set of counties, like all 45. So for example person selects county3, county14 it would return records that have those counties individually plus any records that have all counties (county1, county2, county3... county45). Thank you so much for your assistance! – user3738691 Jun 13 '14 at 19:34
  • @user3738691 Ask a new question about that with example data as that isn't clear to me what your table schema is or what you mean. – Martin Smith Jun 13 '14 at 20:11
  • OK, I've created a new question here: http://stackoverflow.com/questions/24215531/sql-query-using-fn-split-to-find-multiple-values-in-column I'll mark your answer as selected as it definitely helped and was probably the best possible considering how I worded it. Thanks! – user3738691 Jun 14 '14 at 00:43