So far we’ve checked for equals and not equals, and several other operators, on an exact match of a value.

What if we want to perform a partial match?

We can do that in SQL with the LIKE keyword.

The LIKE keyword in SQL allows us to perform a partial match on a value that is provided, where we can use wildcards in place of other values to perform a partial match search.

We can find all values that start with A, for example, no matter what the actual value is.

WHERE expression LIKE comparison_value

The query will check that the expression matches the logic in the comparison value.

So how can we use the LIKE keyword to look for partial matches?

We use wildcards.

Wildcards are special characters in SQL that represent other characters.

There are two: an asterisk * and an underscore _.

So, an asterisk represents zero/one/many of any character, and an underscore represents one of any character.

How To Use Wildcards in SQL

Using wildcards in SQL means you add the characters inside a string you’re checking.

They can go at the start, end, in the middle: anywhere in the string. It just depends on what result you’re looking for.

An example of adding a wildcard to the end of the string would be ‘Ad*’. We are checking for a value of ‘Ad*’, the database will look for values that start with ‘Ad’ and any number of characters after it. It will find values such as ‘Adam’, ‘Adela’ and ‘Ado’ but not ‘Amy’ (because it doesn’t start with ‘Ad’).

Our query would look like this:

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'Ad*';

We could also look for a value of ‘*son’. This will find all values that end in ‘son’ with any number of characters coming before it. It would find values such as ‘Donaldson’, ‘Johnson’ and ‘Simpson’ but not ‘Song’ as it doesn’t end in ‘son’.

SELECT first_name, last_name
FROM customer
WHERE last_name LIKE '*son';

We can use a wildcard in the middle of a string. For example, a string of ‘A*d’ will find all values that start with A, have any number of characters in the middle, and end with ‘d’.

It would find values such as ‘And’, ‘Almond’ and ‘Armoured’ but not ‘Antidote’.

SELECT first_name, last_name
FROM customer
WHERE last_name LIKE 'A*d';

An underscore is used to find a single character. It can be used anywhere in the string, just like an asterisk.

For example, a string of ‘_on’ will find values such as ‘Son’ and ‘Don’ but not ‘Spoon’ (as it contains more than one character).

A query would look like this:

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE '_on';

Using wildcards can be a good way to find the data you need if it’s not an exact match.

Can I Use LIKE with IN?

Unfortunately we can’t. IN uses an exact match and is equivalent to =. LIKE is a partial match.

IN doesn’t support a list of partial match keywords.

To check multiple partial-match values, you would have to use separate WHERE clauses:

WHERE expression LIKE string1
OR expression LIKE string2

Or, you could use a regular expression, if the value can be expressed that way. I’ve written about regular expressions here if you want more information.

More Information

Here are some more articles on the LIKE keyword:

• T-SQL (SQL Server) documentation
• SQL ‘LIKE’ query using ‘%’ where the search criteria contains ‘%’
• Tuning SQL LIKE using indexes

Copyright de Ben Brumm - DatabaseStar