The SQL LIKE clause is used to search for a specified pattern in a column. The SQL LIKE clause is another keyword that is used in the SQL WHERE clause.
The SQL LIKE clause can be used in any valid SQL statement – select, insert, update, or delete. The SQL LIKE clause syntax:
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
We will use the Customers table to illustrate the SQL LIKE clause usage:
| FirstName | LastName | DOB | Phone | |
| John | Smith | John.Smith@yahoo.com | 2/4/1968 | 626 222-2222 |
| Steven | Goldfish | goldfish@fishhere.net | 4/4/1974 | 323 455-4545 |
| Paula | Brown | pb@herowndomain.org | 5/24/1978 | 416 323-3232 |
| James | Smith | jim@supergig.co.uk | 20/10/1980 | 416 323-8888 |
The SQL LIKE clause is very useful when you want to specify a search condition within the SQL WHERE clause, based on a part of a column contents. For example if want to select all customers having last name starting with ‘J’.
SELECT * FROM Customers WHERE LName LIKE ‘J%’
Here is the result of above:
| FirstName | LastName | DOB | Phone | |
| John | Smith | John.Smith@yahoo.com | 2/4/1968 | 626 222-2222 |
| James | Smith | jim@supergig.co.uk | 20/10/1980 | 416 323-8888 |
The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
- ‘ABC%’: All strings that start with ‘ABC’. For example, ‘ABCD’ and ‘ABCABC’ would both satisfy the condition.
- ‘%XYZ’: All strings that end with ‘XYZ’. For example, ‘WXYZ’ and ‘ZZXYZ’ would both satisfy the condition.
- ‘%AN%’: All string that contain the pattern ‘AN’ anywhere. For example, ‘LOS ANGELES’ and ‘SAN FRANCISCO’ would both satisfy the condition.
Another wildcard character is ‘_’ representing any single character. And the ‘[]‘ specifies a range of characters. Have a look at the following SQL syntax:
SELECT * FROM Customers WHERE Phone LIKE ‘[4-6]_6%’
This SQL syntax will return all customers satisfying the following conditions:
- The Phone column starts with a digit between 4 and 6 ([4-6]).
- Second character in the Phone column can be anything (_).
- The third character in the Phone column is 6 (6).
- The remainder of the Phone column can be any character string (%).
Here is the result:
| FirstName | LastName | DOB | Phone | |
| John | Smith | John.Smith@yahoo.com | 2/4/1968 | 626 222-2222 |
| Paula | Brown | pb@herowndomain.org | 5/24/1978 | 416 323-3232 |
| James | Smith | jim@supergig.co.uk | 20/10/1980 | 416 323-8888 |
Related Posts:
