How to Use the LIKE Operator in SQL?

The SQL LIKE operator is a powerful tool for searching and filtering data in a database. It allows you to perform pattern matching on text values within a table.

In this article, we'll explore how to use the LIKE operator in SQL, the different wildcard characters it supports, and practical examples of its application.

Table of Contents

  1. Introduction to the LIKE Operator
  2. The '%' Wildcard
  3. The '_' Wildcard
  4. Using the LIKE Operator with Examples
  5. Conclusion

Introduction to the LIKE Operator

The LIKE operator is primarily used in SQL for pattern matching in text data. It allows you to search for a specified pattern within a column of text data.

The patterns can include wildcard characters that represent unknown or variable characters. The two most commonly used wildcard characters are % and _.

The LIKE operator is often used in conjunction with the SELECT statement to retrieve data that matches a specific pattern.

The '%' Wildcard

The % wildcard is used to match any number of characters (including zero characters). Here are some common use cases for the % wildcard:

The '_' Wildcard

The _ wildcard is used to match a single character. It can be helpful when you want to find records with specific characters at defined positions in the data. Here are some common use cases for the _ wildcard:

Using the LIKE Operator with Examples

Let's explore some practical examples of using the LIKE operator in SQL:

Example 1: Basic Pattern Matching

SELECT * FROM Products
WHERE ProductName LIKE 'Apple%';

This SQL statement retrieves all products whose names start with "Apple."

Example 2: Pattern Matching Anywhere

SELECT * FROM Customers
WHERE Email LIKE '%gmail.com';

This SQL statement retrieves all customers with email addresses ending in "gmail.com."

Example 3: Using Multiple Wildcards

SELECT * FROM Words
WHERE Word LIKE 'b_b';

This SQL statement retrieves all three-letter words where the first and last characters are "b."

Conclusion

The SQL LIKE operator is a powerful tool for pattern matching in text data. By using the % and _ wildcards, you can create flexible and precise search criteria to filter and retrieve data from your database.

Whether you're looking for records that start with a specific pattern, contain certain characters anywhere in the text, or match defined character positions, the LIKE operator allows you to perform complex searches efficiently.

Understanding and using the LIKE operator is a valuable skill for SQL developers and database administrators.