Search code examples
sql-servert-sqlwildcardsql-likewhere-clause

"SQL Question: How can I store multiple wildcard strings in a table and pass them on to the LIKE operator in a where clause"

Here goes my scenario: I have a table say: dbo.Filters(FilterName vacrchar(100),Category varchar(100)) sample values are as below

enter image description here

I use this table to store sample strings which are supposed to be used with wild card search with the like operator in a where cause and there are several of them to be used in different conditions /case statements based on the category they are in , so I cannot just simply use where with and/or conditions instead I have to store them in a table and these strings keep changing frequently. So, the only way for me is to categorize them and use the category as a part of my filter condition and pass the corresponding values into the where clause. It has to be used for something like below but from a table instead of just mentioning them in the query:

select *
from dbo.AnotherTable 
where [freeformtextfield] like ('%I have reached%','%I've reached%','%I reached%')

-- my filters for reached category/ not reached

Solution

  • You can also use EXISTS 

    select  *
from    dbo.AnotherTable
where   exists (select  1 from  Filters where   [freeformtextfield] like '%' + FilterName + '%')