LIKE in SQL

LIKE in SQL

Introduction The LIKE operator in SQL is used to search for a specified pattern within a column. It is commonly used in conjunction with the % and _ wildcard characters to match patterns.

Syntax The basic syntax for using the LIKE operator in SQL is as follows:

sql
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Pattern Matching with Wildcard Characters The wildcard characters % and _ are used in the LIKE operator to match patterns in the column value.

• The % character represents zero, one, or multiple characters.
• The _ character represents a single character.

Examples

Let's consider the following examples to illustrate the usage of the LIKE operator:

1. Matching a Specific Pattern

sql
SELECT *
FROM employees
WHERE first_name LIKE 'J%';

This query will retrieve all employees whose first names start with the letter 'J'. The % wildcard allows for any number of characters after 'J'.

2. Matching Patterns with Wildcards

sql
SELECT *
FROM products
WHERE product_name LIKE '%apple%';

This query will retrieve all products with the word 'apple' anywhere in their names. The % wildcard before and after 'apple' allows for any number of characters before and after it.

3. Matching a Single Character

sql
SELECT *
FROM customers
WHERE last_name LIKE '_o%';

This query will retrieve all customers whose last names have two characters, where the second character is 'o' and can be followed by any number of characters. The _ wildcard matches a single character.

4. Escaping Wildcard Characters

sql
SELECT *
FROM orders
WHERE order_description LIKE '%\_%' ESCAPE '\';

In some cases, you might need to search for the actual wildcard characters % or _ in the column value. In such cases, you can escape the wildcard characters using the ESCAPE keyword and specify the escape character. In this example, we're searching for the literal string '_%' in the order description.

Conclusion The LIKE operator in SQL provides a powerful tool for pattern matching within column values. By using wildcard characters like % and _, you can search for specific patterns and retrieve the desired data from your database tables.