SQL Book

SQL LIKE - flexible string matching

The SQL LIKE operator is often used in the WHERE clause to find string matches on part of a column value or string by using a wildcard character. We can use the LIKE clause to find useful matches such as:

  • Returning all names that start with the letter A
  • Finding all phone numbers with the sequence of digits 1234 in
  • Finding all street names ending in 'Avenue'

In this article we will see how the SQL LIKE operator works and how all the examples just listed can be achieved. The syntax of the LIKE operator in a SELECT statement is as follows:

SELECT FROM WHERE LIKE

Let us assume we have a table of customers as follows:

FirstName LastName Street Telephone
Bob Marley Lime Avenue 020 299922
Jimi Hendrix Albert Road 020 56421
Fred Bloggs Manor Avenue 010 123466

To find all records whose FirstName begins with 'J' then we our WHERE clause would look like:

SELECT *
FROM Customer
WHERE FirstName LIKE 'J%'

Note the wildcard character % which means 'any number of characters (including 0) of any value'. This means that in the above example as long as the value in the FirstName column starts with J then the row is returned.

FirstName LastName Street Telephone
Jimi Hendrix Albert Road 020 56421

It is important to note that different databases use different characters for the wildcard character. Microsoft SQL Server uses % whereas Microsoft Access uses the * character as its wildcard character.

You can put more than one wildcard character in your LIKE condition. This means we can find a string in the middle of a column value:

SELECT *
FROM Customer
WHERE Telephone LIKE '%1234%'

This would return all rows which had the string '1234' somewhere in the Telephone column:

FirstName LastName Street Telephone
Fred Bloggs Manor Avenue 010 123466

To find rows where a column ends in a certain string we can use the wildcard at the beginning of the LIKE condition:

SELECT *
FROM Customer
WHERE Street LIKE '%Avenue'

This example returns all rows where the value in the Street column ends in 'Avenue':

FirstName LastName Street Telephone
Bob Marley Lime Avenue 020 299922
Fred Bloggs Manor Avenue 010 123466

Specifying a range of characters in the LIKE condition

You can specify a range of characters in the LIKE condition by entering the first and last characters of the range (seperated by a -) within square brackets. The range of characters can be a letter range or a numeric range, i.e. [q-w] or [2-6]:

WHERE FirstName LIKE '[A-F]%'

The above example would return all records where the value in the FirstName column began with either A, B, C, D, E or F as specified by indicating the range [A-F] followed by the wildcard character %.

FirstName LastName Street Telephone
Bob Marley Lime Avenue 020 299922
Fred Bloggs Manor Avenue 010 123466

One common mistake people use when using the LIKE operator is to put the wildcard character outside the single quotation marks that delimit the like condition, i.e. WHERE FirstName LIKE 'J'%

Conclusion

In this article we have demonstrated a number of uses of the SQL LIKE operator and given real world examples. We have seen how the wildcard character can enable the LIKE operator to perform powerful string matching abilities that are a lot more flexible than using the = (equals) operator.

With the LIKE operator we can search within column values rather than matching the whole column value. We can also specify ranges of characters to match a character to.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.