Query a SQL field for whether it contains any one of multiple values

Question

I want to query the rows of SQL column for whether it contains any one of multiple values.

For instance, return rows of a table where its column A contains any of the following words: ('cow','farmer','milk').

Easy enough when you know what the words are, but I want to write a sproc where I can feed in any array of strings and if the column A for a certain row contains any of them, it would return the row.

I would want to be able to feed in:

('cow','farmer','milk')

or

('cow','farmer','steak','yikes')

or

('cow','farmer','three', 'pigs', 'wolf')

It must be relatively straightforward, but I cannot for the life of me figure it out. I'm on SQL Server 2008

Answer 1

One simple approach:

declare @candidates varchar = '|cow|farmer|three|pigs|wolf|'
select * from TableName where @candidates like '%|' + FieldName + '|%'

The best way to do this in SQL 2008 is with a table-valued parameter.

Answer 2

I had the same dilema, and came up with the following. For example, if table_a contains ('My Cow', 'My Goat', 'My Dog') and table_b contains ('Dog', 'Cow'), then...

create table #table_a (field1 varchar(128))
insert into #table_a values('My Cow')
insert into #table_a values('My Goat')
insert into #table_a values('My Dog')

create table #table_b (field1 varchar(128))
insert into #table_b values('Dog')
insert into #table_b values('Cow')

select * from #table_a table_a where (select count(*) from #table_b table_b where charindex(table_b.field1, table_a.field1) > 0) > 0

returns ('My Cow','My Dog')

Hope this helps.