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.

Examples

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 email that do not end with the domain "gmail.com":

SELECT * 
FROM customers 
WHERE email 
NOT LIKE '%@gmail.com';

Best Practices

• When using the LIKE operator 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 LIKE operator to match patterns in strings. The most commonly used wildcards are % and _.

• The % 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 name column starts with the letter "J":

SELECT * 
FROM table_name 
WHERE name LIKE 'J%';

• The _ wildcard is used to match a single character in a string. For example, the following query would return all rows where the name column 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 LOWER() or UPPER() function on the column and pattern.

• When using the LIKE operator, 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 ESCAPE clause in a LIKE statement 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 ESCAPE clause 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.