'LIKE' clause with 'AND' operator in SQL Server 2012
I have exactly the same requirement as in this thread. How to use JOIN LIKE with AND Operator in SQL Server?
I have been clueless despite searching the net for hours.
I have a table ‘Filter’ with column ‘Value’ and 2 records in it as ‘Value1’ and ‘Value2’ as varchar values.
Select * From MasterTable Inner Join Filter ON MasterTable.Name LIKE '%' + Filter.Value + '%'
This query means:
Select * From MasterTable Where MasterTable.Name LIKE '%Value1%' OR MasterTable.Name LIKE '%Value2%'
Now how can I change JOIN LIKE that means AND not OR? Something like:
Select * From MasterTable Where MasterTable.Name LIKE '%Value1%' AND MasterTable.Name LIKE '%Value2%'
Can anyone show me a way out.
One Solution collect form web for “'LIKE' clause with 'AND' operator in SQL Server 2012”
You can generally rewrite a “X matches all Y” query as “X does not have any Y that do not match” – or in other words a
NOT EXISTS query for the inverse of Y.
select * from MasterTable where not exists ( select 1 from Filter where MasterTable.Name not like '%' + Filter.Value + '%' )