How do you use the LIKE operator with wildcard characters (%) to perform pattern matching in SQL?

Question

Aryan Kumar · Answer

The LIKE operator in SQL is used to perform pattern matching. It can be used with wildcard characters to match any number of characters, any single character, or a specific range of characters.

The two most commonly used wildcard characters are:

% - Matches zero or more characters.
_ - Matches one single character.

For example, the following query will return all rows from the products table where the product name starts with the letter "A":

SQL

SELECT *
FROM products
WHERE product_name LIKE 'A%';

The % wildcard character matches zero or more characters, so this query will return all products that start with the letter "A", regardless of how many other characters are in the name.

The following query will return all rows from the products table where the product name contains the letter "a":

SQL

SELECT *
FROM products
WHERE product_name LIKE '%a%';

The % wildcard character can be used anywhere in the pattern, so this query will return all products that contain the letter "a", regardless of where it is in the name.

The _ wildcard character matches one single character, so the following query will return all rows from the products table where the product name starts with the letter "A" and ends with the letter "s":

SQL

SELECT *
FROM products
WHERE product_name LIKE 'A_s';

The _ wildcard character can only be used once in a pattern, so this query will only return products that match the exact pattern specified.

The LIKE operator can also be used with other wildcard characters, such as [], which matches any single character in a specified set. For example, the following query will return all rows from the products table where the product name starts with the letter "A" and ends with a digit:

SQL

SELECT *
FROM products
WHERE product_name LIKE 'A[0-9]';

The [0-9] wildcard character matches any single digit, so this query will return all products that match the exact pattern specified.

The LIKE operator is a powerful tool that can be used to perform pattern matching in SQL. By using wildcard characters, you can match any number of characters, any single character, or a specific range of characters. This can be used to find specific rows in a table or to filter results based on a pattern.

forum

How do you use the LIKE operator with wildcard characters (%) to perform pattern matching in SQL?

Can you answer this question?

1 Answers