In SQL to filter data based on a specific pattern, the LIKE clause is used in the WHERE condition. It can be applied to date, string, or numeric values. However, using the string values is advised.

Uses of LIKE clause in SQL:

The following are some examples of how the LIKE clause is used:

• Enables us to extract information that adheres to the necessary pattern.
• Helps us to perform complex regex-based queries on our data.
• Reduces the complexity of the queries.

Wildcard operators:

The LIKE operator in SQL supports two wildcard characters:

1. The percent sign (%) represents zero, one, or more than one character. It is frequently used to match any string of characters at the start, end, or within the string itself.
2. The underscore sign (_) represents only a single character. It is typically used in a pattern to match a specific character in a string at a specific position.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE columnN LIKE pattern;

Example:

Consider the following Product table.

ID	ITEM	PRICE
101	Jar	250.00
102	Basketball	500.00
103	Chair	2500.00

The following table has a few examples showing the WHERE part having different LIKE clause with ‘%’ and ‘_’ operators −

SELECT * FROM Product WHERE PRICE LIKE 'XXX';

Statement	Description
WHERE PRICE LIKE ‘25%’	Finds any values that start with 25.
WHERE PRICE LIKE ‘%00%‘	Finds any values that have 00 in any position.
WHERE PRICE LIKE ‘_00%’	Finds any values that have 00 in the second and third positions.
WHERE PRICE LIKE ‘2_%_%‘	Finds any values that start with 2 and are at least 3 characters in length.
WHERE PRICE LIKE ‘%2’	Finds any values that end with 2.
WHERE PRICE LIKE ‘_5%0‘	Finds any values that have a 5 in the second position and end with a 0.

Outputs will be generated as per the input query.

Note: also read about SQL: WHERE clause

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py