SQL LIKE Example | LIKE Operator In SQL
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:
- % – Percent sign represents zero, one, or multiple characters.
- _ – Underscore represents a single character.
SQL LIKE Operator
The SQL LIKE is the logical operator that determines 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.
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_'
In the above syntax, after the LIKE Operator, there is the pattern.
#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:
- A percent wildcard (%): Any string of zero or more characters.
- An underscore (_) wildcard: Any single character.
- Awildcard: Any single character within a specified set.
- A [character-character]: Any single character within the specified range.
- 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.
The escape character instructs a LIKE operator to treat the wildcard characters as the regular characters. An escape character has no default value and must be evaluated to only one character.
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.
|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 that have 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 in length.
|5||WHERE ProductName LIKE ‘%s’|
It finds any values that end with s.
|6||WHERE ProductName LIKE ‘_V%s’|
It finds any values that have a V in the second position and end with an s.
Finally, SQL LIKE Operator example is over.