SQL LIKE

What is SQL LIKE?

The SQL LIKE operator is used to search for a specified pattern within a column in a database table. It allows you to perform partial matching and pattern matching, making it useful for searching text data. The LIKE operator is often used with wildcard characters to create flexible search conditions.

When you would use it

You would use the SQL LIKE operator when you need to search for values that match a specific pattern within a text or character column. This is useful when you want to filter rows that contain specific substrings, patterns, or characters. Common use cases include searching for names, keywords, or filtering text data based on specific criteria.

Syntax

The syntax for using the LIKE operator is as follows:

SELECT columns
FROM table_name
WHERE column_name LIKE pattern;

• columns: The columns you want to retrieve in the query.
• table_name: The name of the table containing the data.
• column_name: The name of the column you want to search.
• pattern: The search pattern you want to match using the LIKE operator. This pattern can include wildcard characters like % and _.

Parameter values

• columns: The columns you want to retrieve in your query.
• table_name: The name of the table where the data is stored.
• column_name: The name of the column you want to search.
• pattern: A text pattern that specifies the search criteria. It may include wildcard characters to create flexible matching conditions.

Example query

Suppose we have a table named "products" with columns "product_id" and "product_name." We want to retrieve products whose names contain the word "Phone" anywhere in the name:

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%Phone%';

In the above query, we use the LIKE operator with % wildcard characters to match products with "Phone" anywhere in the name.

Example table response

Assuming the "products" table contains the following data:

| product_id | product_name          |
|------------|----------------------- |
| 1          | Smartphone            |
| 2          | Phone Case            |
| 3          | Laptop                |
| 4          | Cordless Phone Set    |
| 5          | Tablet                |

The query mentioned earlier would return the following result:

| product_id | product_name    |
|------------|----------------- |
| 1          | Smartphone      |
| 2          | Phone Case      |
| 4          | Cordless Phone Set |

This result includes products with names containing the word "Phone."

Use cases

• Searching for specific words, phrases, or patterns within text data.
• Filtering data based on partial matches or patterns in text columns.
• Creating flexible and customizable search conditions in a query.

SQL languages this is available for

The SQL LIKE operator is a standard SQL feature and is available in most relational database management systems (RDBMS) that support SQL. This includes popular RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.