SQL LIKE Operator: Complete Guide

SQL LIKE Operator is used in the WHERE clause to search for the specified pattern in a column. There are two wildcards often used in conjunction with a LIKE operator:

1. % – The percent sign represents zero, one, or multiple characters.
2. _ – Underscore represents a single character.

SQL LIKE Operator

SQL LIKE  is a logical operator determining if the character string matches the specified pattern. The pattern may include the regular characters and wildcard characters.

SQL Like Operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching.

Syntax

SELECT FROM table_name
WHERE column LIKE 'AAAA%'

or 

SELECT FROM table_name
WHERE column LIKE '%AAAA%'

or

SELECT FROM table_name
WHERE column LIKE 'AAAA_'

or

SELECT FROM table_name
WHERE column LIKE '_AAAA'

or

SELECT FROM table_name
WHERE column LIKE '_AAAA_'

After the LIKE Operator, there is a pattern in the above syntax.

The pattern in SQL LIKE

The design is the sequence of characters to search for in a column or expression. It can contain the following valid wildcard characters:

1. A percent wildcard (%): Any string of zero or more characters.
2. An underscore (_) wildcard: Any single character.
3. A [list of characters] wildcard: Any single character within a specified set.
4. A [character-character]: Any single character within the specified range.
5. A [^]: any single character not within a list or the range.

The wildcard characters make the LIKE Operator more flexible than the equal (=) and not equal (!=) string comparison operators.

Escape character

The escape character instructs a LIKE operator to treat the wildcard characters as regular characters. An escape character has no default value and must be evaluated to only one character.

Examples

Let’s say we have the following table.

Now, we will use the LIKE Operator to query the database. See the following query.

SELECT * FROM Products
WHERE ProductName LIKE 'LV%';

See the following output.

More Statements

Sr.No.	Statement & Description
1	WHERE ProductName LIKE ‘LV%’ It finds any values that start with LV.
2	WHERE ProductName LIKE ‘%TIA%’ It finds any values that have TIA in any position.
3	WHERE ProductName LIKE ‘___INI%’ It finds any values with INI in the fourth, fifth, and sixth positions.
4	WHERE ProductName LIKE ‘L_%_%’ It finds any values that start with 2 and are at least 3 characters long.
5	WHERE ProductName LIKE ‘%s’ It finds any values that end with s.
6	WHERE ProductName LIKE ‘_V%s’ It finds any values with a V in the second position and ends with an s.

That’s it for this tutorial.