Antelion
Antelion

Reputation: 245

LIKE operator with a variable number of search conditions, in T-SQL

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

Answers (4)

paparazzo
paparazzo

Reputation: 45096

SQL Fiddle:

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

Jesuraja
Jesuraja

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

Kevin Cook
Kevin Cook

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

Linger
Linger

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

Related Questions