Equivalent of 'IN Like % subquery %' to match multiple values

[RobS23]

I'm in the bind that every search I have to do is embedded within a string. Therefore I cannot use 'IN' against a subquery list of values.

For example my target field contains the equivalent of:

'Slow Blue car with red trim'

My table of search values contain

'Red'
'Blue'
'Green'

as long as I hit one of the search values within the target string I want it returned in the query.

Sure, I could put together a cursor or I could even build and execute some dynamic SQL - but - we all know that they're slow and we like parameters and their stats etc.

Secondly, the example above is just for clarity there is NO chance of me parsing the strings out into columns and matching that way.

Now, I hope I'm missing some neat function that will allow me to use a 'IN like % [my subquery] %' methodology. Come on one of Guru's sort me out!

 

[SQLSister]

YOu need to institute full text search. Read about it in BOL.

Questions about posting. See faq183-874

 

[vongrunt]

If I understand correctly:

select * from myTable
where exists( select * from searchValues where myTable.myColumn like '%' + value + '%')

myTable.myColumn is target column
searchValues is table containing words to search (column value).

Note: this will also select "Fred" and "Blueberry" and "Greenhouse"...

 

[RobS23]

Thankx SQL Sister - I knew it damnit. Still, I suppose it's good to know that I wasn't missing something obvious.

 

[SQLSister]

Believe me it will much faster than any like %text% search. It's not really hard once you get it set up and understand how it works.

Questions about posting. See faq183-874