Using SQL’s LIKE predicate against a list of search strings

A colleague asked me recently if it was possible to select a set of rows using the LIKE predicate against a potentially large list of search strings, without repeating the LIKE for each string. It turns out it is possible using the two methods below.

Common Table Expressions (CTEs)

Before we get stuck into the examples, a word on Common Table Expressions (using the WITH clause). I’m a big fan of using Common Table Expressions to try to make my SQLs more readable, and in the examples below I have used them to “lift” the subqueries out of the main SQL body, hopefully making everything a bit more readable. However, they are not necessary for the techniques to work and the contents of the CTEs could just as easily be embedded directly where the CTE is referenced or replaced by tables if the search criteria were table driven. At the end of this post are some examples that don’t use CTEs.

Test Data

First let’s create a simple test table with some data to experiment with. We’ll scan this table for countries that match a list of specified search strings.

/* Create some test data. */
 declare global temporary table COUNTRIES as (
     select *
     from(
         values('England'),
               ('Ireland'),
               ('Scotland'),
               ('Wales'),
               ('France'),
               ('Germany'),
               ('Italy'),
               ('Spain'),
               ('Austria'),
               ('Belgium'),
               ('Holland')
     ) x(COUNTRY)
 ) with data

 

Method 1 – Using JOIN with LIKE

As I mentioned, I use a Common Table Expression (CTE), named SEARCH_STRINGS, to generate the list of search strings but they could just as easily be embedded as a subquery in the join or come from another table. The CTE is JOINed to the table to be scanned using the LIKE predicate on the column to be scanned, as follows:

/* Scan for countries whose name is LIKE any of the search strings. */
with SEARCH_STRINGS as (
    select '%' concat SEARCH_STRING concat '%' as SEARCH_STRING 
    from(
        values('EN'), 
              ('LY'), 
              ('LAND'), 
              ('SOUTH')
    ) x(SEARCH_STRING)
)

select * 
from COUNTRIES 
join SEARCH_STRINGS 
    on upper(COUNTRY) like SEARCH_STRING

This has the benefit of showing which countries matched which search string, but at the expense of showing duplicates where more than one of the search strings matches.

For our single column table this can easily be solved with the DISTINCT keyword.

/* Scan for countries whose name is LIKE any of the search strings. */
 with SEARCH_STRINGS as (
     select '%' concat SEARCH_STRING concat '%' as SEARCH_STRING
     from(
         values('EN'),
               ('LY'),
               ('LAND'),
               ('SOUTH')
     ) x(SEARCH_STRING)
 )

select distinct(COUNTRY)
from COUNTRIES
join SEARCH_STRINGS
     on upper(COUNTRY) like SEARCH_STRING

However, a better way of selecting distinct matches is to use Method 2 below.

Method 2 – Using EXISTS with a Subquery

This time the CTE of search strings is used as part of an EXISTS subquery which uses the LIKE predicate against the outer SQL’s scan column (COUNTRY), together with the SEARCH_STRING from the CTE of search strings. This results in only one row being selected from the COUNTRIES table when any of the search strings match.

with SEARCH_STRINGS as (
    select '%' concat SEARCH_STRING concat '%' as SEARCH_STRING
    from(
        values('EN'),
              ('LY'),
              ('LAND'),
              ('SOUTH')
    ) x(SEARCH_STRING)
)

select *
from COUNTRIES
where exists (
    select *
    from SEARCH_STRINGS
    where upper(COUNTRY) like SEARCH_STRING
)

Of course, we can negate the WHERE clause to find all countries that do not match any of the search strings:

select *
from COUNTRIES
where not exists (
    select *
    from SEARCH_STRINGS
    where upper(COUNTRY) like SEARCH_STRING
)

Examples of Both Methods Without CTEs

Method 1

select *
from COUNTRIES
join (
    select '%' concat SEARCH_STRING concat '%' as SEARCH_STRING
    from(
        values('EN'),
              ('LY'),
              ('LAND'),
              ('SOUTH')
    ) x(SEARCH_STRING)
) SEARCH_STRING
    on upper(COUNTRY) like SEARCH_STRING

Method 2

select *
from COUNTRIES
where exists (
    select *
    from(
        values('EN'),
              ('LY'),
              ('LAND'),
              ('SOUTH')
    ) x(SEARCH_STRING)
    where upper(COUNTRY) like '%' concat SEARCH_STRING concat '%'
)