In This Article, You Will Know About SQL LIKE Operator.
SQL Functions – Before moving ahead, let’s know a bit about SQL MIN() and MAX() Functions.
Table of Contents
SQL LIKE operator
The SQL LIKE operator is used with the WHERE clause to find out the specified pattern in the column.
Two most popular wildcards are used with the LIKE operator:
(%) – The percentage (%) sign is used to match zero, one, or multiple characters.
(_) – The underscore sign is used to represent one or a single character.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;
Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
LIKE Operator | Description |
WHERE CustomerName LIKE ‘A%’ | Finds any values that start with “A” |
WHERE CustomerName LIKE ‘%x’ | Finds any values that end with “x” |
WHERE CustomerName LIKE ‘%e%’ | Finds any values that have “e” in any position |
WHERE CustomerName LIKE ‘_l% | Finds any values that have “_l” in the second position |
WHERE CustomerName LIKE ‘R____%’ | Finds any values that start with “R” and are at least 5 characters in length |
WHERE ContactName LIKE ‘A%x’ | Finds any values that start with “A” and ends with “x” |
Example Database
Below is the example of a database table named “details.”
Id | Name | Class | section_id |
1 | Alex | 5 | 1 |
2 | Rolex | 6 | 2 |
3 | Salex | 7 | 2 |
4 | Lelex | 8 | 3 |
5 | Kelex | 9 | 5 |
SQL LIKE Examples
Example: Use LIKE operator to return the match starts with ‘A’.
SELECT * FROM details
WHERE Name LIKE 'A%';
As a result, it returns the all names starts with “A” from the whole table.
Example: Use LIKE operator to return the match starts with ‘S’.
SELECT Name
FROM details
WHERE Name LIKE 'S%';
As a result, it returns the all names starts with “S” from the “Name” column.
Example: Use LIKE operator to return the match ends with ‘x’.
SELECT Name
FROM details
WHERE Name LIKE '%x';
As a result, it returns the all names ends with “x” from the “Name” column.
Example: Use LIKE operator to return the match have ‘e’ character at any position.
SELECT Name
FROM details
WHERE Name LIKE '%e%';
As a result, it returns the all names that contains “e” at any position from the “Name” column.
Example: Use LIKE operator to return the match have ‘l’ character in the second position.
SELECT * FROM details
WHERE Name LIKE '_l%';
As a result, it returns the all names that contains second character “l” from the whole table.
Example: Use LIKE operator to return the match have ‘R’ character in the first position.
SELECT * FROM details
WHERE Name LIKE 'R____%';
As a result, it returns the all names from the whole table that starts with character “R” and have at least total 5 characters in the length.
Example: Use LIKE operator to return the match have ‘R’ character in the first position, ‘l’ at any position, and ‘x’ at the last position.
SELECT * FROM details
WHERE Name LIKE 'R__l__x%';
As a result, it returns the all names from the whole table that starts with character “R” and have at least total 5 characters in the length.
Example: Use LIKE operator to return the match have ‘A%x’ character “A” in the starting and “x” at the last.
SELECT * FROM details
WHERE Name LIKE 'A%x';
As a result, it returns the all names from the whole table that starts with character “A” and end with character “x”.
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
