Reputation: 245
Is there a short way to look for multiple matches with the LIKE operator, using AND clauses?
I should do it dynamically, with a variable number of terms. Obtaining it statically (with a fixed number of terms) is a no-brainer:
SELECT *
from MyTable
WHERE MyColumn LIKE "%AAA%"
AND MyColumn LIKE "%BBB%"
AND MyColumn LIKE "%CCC%"
Let's assume there is a table variable that contains an unknown number of terms:
DECLARE @Terms table
(
Term nvarchar(500)
)
Is there a way to perform the LIKE statement on MyColumn matching all the items in @Terms?
Upvotes: 3
Views: 1002
Reputation: 45096
select mt.*
from MyTable mt
join @Terms t
on mt.MyColumn like '%' + t.Term + '%'
group by mt.MyColumn
having COUNT(*) = (select COUNT(*) from @Terms)
Upvotes: 1
Reputation: 3844
Try this:
SELECT MT.*
FROM MyTable AS MT INNER JOIN
@Terms AS TR ON CHARINDEX(TR.Term, MT.MyColumn) > 0
GROUP BY MT.MyColumn
HAVING COUNT(*) = (SELECT COUNT(*) FROM Terms)
Upvotes: 0
Reputation: 1932
DECLARE @Terms TABLE
(
WildCards VARCHAR(20)
)
INSERT INTO @Terms
( WildCards )
VALUES
( 'CO' ),
( 'DO' ),
( 'EO' )
DECLARE @FoundAll INT
SELECT @FoundAll = Count(*) FROM @Terms
SELECT mt.MyColumn, COUNT(*), @FoundAll FROM MyTable mt
OUTER APPLY
(
SELECT WildCards FROM @Terms
) d
WHERE mt.MyColumn LIKE ('%' + d.WildCards + '%')
GROUP BY mt.MyColumn
HAVING COUNT(*) = @FoundAll
This will only pull the record which matches ALL of the possible likes.
Upvotes: 3
Reputation: 15058
If you can get away with not using the LIKE
then the following would work:
SELECT *
FROM MyTable
WHERE MyColumn IN
(
SELECT Term
FROM Terms
)
Or, how about the following (SQL Fiddle):
SELECT *
FROM MyTable
INNER JOIN Terms
ON MyTable.MyColumn LIKE '%' + Terms.Term + '%'
Upvotes: 0