SQL -Structured Query Language - Sql Clause - Like Clause Tutorial
To retrieve a record having a specific pattern in a record from the existing created table. Then we can use a ' WHERE ' Clause with LIKE Operator.
There are two wildcard operator used with like clause to find a similar pattern in the record-
- % - The percent sign is used when we don’t know how many characters are below and above of specific character.
Suppose in a table
name |
~ Sharad |
~ Saurabh |
Shantanu |
~ Param |
Vaibhav |
We have to retrieve names having specific character “ra”, hence we don’t know how many characters are below and above of specific character “ra”.
Therefore here we used the LIKE “%ra%” Statement.
This will retrieve “Sharad”, “Saurabh” and “Param”. Because this name is matching the given-like condition.
Syntax-
SELECT * FROM table_name WHERE column_name LIKE ‘%specific_character%’;
For example-
SELECT * FROM employees WHERE first_name LIKE '%ra%';
In this statement, it will retrieve the first_name having specific syntax “ra” in it from the table employees.
- _ - The underscore sign is used when we know how many characters are below and above of specific character.
Suppose in a table
name |
Sharad |
Saurabh |
Shantanu |
Param |
~ Vaibhav |
We have to retrieve a name having a specific character “aib”, And there should be only one character below and three characters above the specific character “aib”.
Therefore here we used the LIKE “_ aib_ _ _” Statement.
This will retrieve the name “Vaibhav”. Because “Vaibhav” matches the given like condition.
Syntax-
SELECT * FROM table_name WHERE column_name LIKE ‘_specific_character_ ’;
For example-
SELECT * FROM employees WHERE first_name LIKE '_ar__';
In this statement, it will retrieve the first_name having specific syntax “ar” in it with one character below it and two-character above it, from the table employees.
Other examples-
SELECT * FROM employees WHERE first_name LIKE '_ar%';
SELECT * FROM employees WHERE first_name LIKE '%a_r%';
Table for more clarification on LIKE Clause
Sr.no |
Different format LIKE Clause is used |
Explanation |
1 |
LIKE ‘ab%’ |
It will retrieve the value that starts with “ab” and end with any number of any character |
2 |
LIKE ‘%ab%’ |
It will retrieve the value that having “ab” in between it |
3 |
LIKE ‘%ab’ |
It will retrieve the value that ends with “ab” and start with any number of any character |
4 |
LIKE ‘_ab’ |
It will retrieve the value that ends with “ab” and having only one character below “ab”. |
5 |
LIKE ‘_ab_ _’ |
It will retrieve the value that starts with one character below and end with two characters above the “ab”. |
6 |
LIKE ‘%ab_’ |
It will retrieve the value that ends with one character above “ab” and start with any number of the character below “ab”. |
7 |
LIKE ‘%a_b%’ |
It will retrieve the value that having one character between “a” and “b”, start and end with any number of characters below and above “a_b”. |