SQL LIKE and Wildcards
In SQL, the
LIKE operator is used to match text values against a pattern. The pattern can contain special characters, called wildcards, which can be used to match one or more characters.
The two most commonly used wildcards in SQL are the percent sign
% and the underscore
_. The percent sign is used to match any number of characters (including zero characters), while the underscore is used to match exactly one character.
For example, the following SQL statement would match any values in a column named
last_name that start with the letters "Sm":
SELECT * FROM customers WHERE last_name LIKE 'Sm%';
This query will return all rows where
last_name starts with "Sm" like "Smith", "Smile", "Small", etc
The following SQL statement would match any values in a column named phone_number that have the format "xxx-xxx-xxxx", where x is any digit:
SELECT * FROM customers WHERE phone_number LIKE '___-___-____';
This query will match every phone number which is formatted in the format "xxx-xxx-xxxx"
You can also use the
NOT LIKE operator to find values that do not match a pattern. For example, the following SQL statement would match any values in a column named
SELECT * FROM customers WHERE email NOT LIKE '%@gmail.com';
When using the
LIKEoperator in SQL, it's important to be mindful of the use of wildcards. Wildcards are special characters that can be used in conjunction with the
LIKEoperator to match patterns in strings. The most commonly used wildcards are
%wildcard is used to match any number of characters (including zero characters) in a string. For example, the following query would return all rows where the
namecolumn starts with the letter "J":
SELECT * FROM table_name WHERE name LIKE 'J%';
_wildcard is used to match a single character in a string. For example, the following query would return all rows where the
namecolumn has exactly four letters:
SELECT * FROM table_name WHERE name LIKE '____';
It's a best practice to use these wildcards strategically in order to minimize the number of rows that need to be searched through, as well as to optimize performance and index usage.
Also very important to keep in mind that some SQL engines are case sensitive by default, so if you are using wildcards in a case-sensitive manner you will have to use additional conditions in the query or use the
UPPER()function on the column and pattern.
When using the
LIKEoperator, it's generally better to use it on the indexed columns in the table to improve query performance.
In some cases, it also is a good practice to use the
ESCAPEclause in a
LIKEstatement when you want to use a wildcard character as a regular character. This is because wildcard characters have special meaning when used with
LIKE, so you'll need to escape them if you want to match them literally. The
ESCAPEclause is used to specify the escape character that will be used in the LIKE statement. For example:
SELECT * FROM table_name WHERE name LIKE '100\%%' ESCAPE '\';
In this example, the
\ character is used as the escape character, so the
% character is treated as a regular character rather than a wildcard, meaning that all rows where the
name column starts with "100%" will be returned.