Pattern Matching with LIKE in SQL

Pattern matching in SQL allows you to search for data that matches a specific pattern, making your queries more flexible. The LIKE operator is used in conjunction with wildcards to perform pattern-based searches. This is particularly useful when you need to find rows that match a partial string or a specific pattern in a column.

1. Understanding the LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It allows you to match text values based on a pattern rather than an exact match.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

column_name: The column you want to search.
pattern: The pattern you want to match, using wildcards to define the flexible parts of the search.

2. Wildcards for Pattern Matching

Wildcards are special characters used in conjunction with the LIKE operator to create patterns. SQL supports two primary wildcards:

%: Represents zero, one, or more characters. It can match any sequence of characters.
_: Represents a single character. It matches exactly one character.

a. Using % Wildcard

The % wildcard is used when you want to match any sequence of characters. This is useful for finding rows where the column contains a certain substring, starts with a specific prefix, or ends with a specific suffix.

Examples:

Match any string that starts with "A":
```
SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE 'A%';
```
- This query retrieves all employees whose first name starts with the letter "A". The % wildcard matches any sequence of characters following "A".
Match any string that ends with "son":
```
SELECT first_name, last_name 
FROM employees
WHERE last_name LIKE '%son';
```
- This query retrieves all employees whose last name ends with "son". The % wildcard matches any sequence of characters preceding "son".
Match any string that contains "mit":
```
SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE '%mit%';
```
- This query retrieves all employees whose first name contains the substring "mit". The % wildcard matches any sequence of characters before and after "mit".

b. Using _ Wildcard

The _ wildcard is used when you want to match exactly one character. This is useful for finding rows where the column contains a specific pattern with a single varying character.

Examples:

Match any string with "J_n":
```
SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE 'J_n';
```
- This query retrieves all employees whose first name is three characters long and starts with "J" and ends with "n". The _ wildcard matches any single character in the middle.
Match any string that has "M" as the second character:
```
SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE '_M%';
```
- This query retrieves all employees whose first name has "M" as the second character, regardless of what comes before or after.

3. Combining Wildcards for Complex Patterns

You can combine the % and _ wildcards to create more complex search patterns.

Example:

SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE 'A_m%';

In this example:

The query retrieves all employees whose first name starts with "A", has "m" as the third character, and can have any sequence of characters after "m".

Result:

first_name

last_name

In this output:

The result includes names that match the pattern "A_m%"—starting with "A", with "m" as the third character.

4. Using NOT LIKE for Exclusion

The NOT LIKE operator is used to exclude rows that match a specific pattern. This is useful when you want to filter out data that meets certain criteria.

Example:

SELECT first_name, last_name 
FROM employees
WHERE first_name NOT LIKE 'A%';

In this example:

The query retrieves all employees whose first name does not start with "A".

5. Case Sensitivity in LIKE

The case sensitivity of the LIKE operator depends on the SQL database you're using:

Case-insensitive: In most databases like MySQL, the LIKE operator is case-insensitive by default, meaning it treats uppercase and lowercase letters as the same.
Case-sensitive: In some databases like PostgreSQL, the LIKE operator can be case-sensitive depending on the collation settings.

If you need a case-insensitive search in a case-sensitive environment, you can use functions like LOWER() or UPPER() to normalize the text before comparison.

Example:

SELECT first_name, last_name 
FROM employees
WHERE LOWER(first_name) LIKE 'a%';

In this example:

The LOWER() function converts the first_name column to lowercase before applying the LIKE pattern, ensuring a case-insensitive search.

6. Practical Use Cases of LIKE

a. Searching for Email Domains

You can use LIKE to search for specific email domains in an email column.

SELECT first_name, last_name, email 
FROM employees
WHERE email LIKE '%@gmail.com';

In this example:

The query retrieves all employees with email addresses that end with "@gmail.com".

b. Finding Names with Specific Patterns

You can use LIKE to find names that match specific patterns, such as names that start with certain letters or contain certain substrings.

SELECT first_name, last_name 
FROM employees
WHERE last_name LIKE 'S%a';

In this example:

The query retrieves all employees whose last name starts with "S" and ends with "a".

7. Escaping Special Characters

If your pattern includes a wildcard character that you want to treat as a literal character (not a wildcard), you can escape it using a backslash \ or an escape character specific to your database.

Example:

SELECT first_name, last_name 
FROM employees
WHERE email LIKE '%\_support@domain.com' ESCAPE '\';

In this example:

The query retrieves emails that literally contain "_support@domain.com" without treating the underscore as a wildcard.

Conclusion

The LIKE operator in SQL is a powerful tool for pattern matching and flexible searching. By using wildcards like % and _, you can create queries that match complex patterns, enabling you to find data that meets specific criteria. Whether you need to search for partial matches, exclude certain patterns, or handle case sensitivity, mastering the LIKE operator is essential for effective SQL querying.

This tutorial is brought to you by codes with pankaj.

PreviousRemoving Duplicates with DISTINCT in SQL NextNULL Handling in SQL

Last updated 2 months ago