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.
Discuss this article in the forums
Tell a friend about this page