LIKE operator

In SQL, the LIKE operator is used to perform pattern matching on a column's value. It is commonly used in the WHERE clause of a query to filter rows based on partial matches or specific patterns in the data.

The LIKE operator is especially useful when searching for data that may have varying characters or when you need to perform wildcard searches. It supports two wildcard characters:

% (Percent Sign): Represents zero or more characters.
_ (Underscore): Represents a single character.

The basic syntax of the LIKE operator is as follows:

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

Here's an example to illustrate its usage:

Suppose we have a table named "Customers" with a column named ContactName. We want to retrieve all customers whose contact name starts with "Joh" followed by any characters.

SELECT CustomerID, ContactName
FROM Customers
WHERE ContactName LIKE 'Joh%';

In this example, the LIKE operator is used with the pattern 'Joh%' to filter rows where the ContactName column starts with "Joh" followed by any characters. The result will include customers with contact names like "John Smith," "John Doe," and so on.

You can also use the % wildcard at the end of the pattern to search for values that end with a specific set of characters. For example:

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%sauce';

In this example, the LIKE operator is used with the pattern '%sauce' to retrieve product names that end with "sauce."

Additionally, you can use the _ (underscore) wildcard to represent a single character. For instance:

In this example, the LIKE operator is used with the pattern 'C_r%' to retrieve product names that start with "C," followed by any single character, and then followed by any characters.

The LIKE operator is case-sensitive in most database systems, but some databases offer options to perform case-insensitive searches. If you need case-insensitive pattern matching, you can use the appropriate functions provided by the specific database system (e.g., ILIKE in PostgreSQL or UPPER() or LOWER() functions with LIKE in some databases).

The LIKE operator is a powerful tool for filtering and searching data based on patterns, making it useful for a wide range of scenarios in SQL queries.