Good morning to all,
Welcome to SQL commnands, queries, operators etc. Now we are discuss the SQL LIKE operator. The LIKE operators used to fetch the data from database foor specific pattern.
The LIKE operator is used to search for a specified pattern in a column used in WHERE clause. There are two wildcards used in conjunction with the LIKE operator:
- The percent sign (%)- represents zero, one or more than one character.
- The underscore (_)-represents only one character
SQL LIKE SYNTAX
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
The Basic Syntax of % and _ is as follows:
SELECT FROM table_name
WHERE column LIKE ‘YYYY%’
or
SELECT FROM table_name
WHERE column LIKE ‘%YYYY’
or
SELECT FROM table_name
WHERE column LIKE ‘%YYYY%’
or
SELECT FROM table_name
WHERE column LIKE ‘YYYY_’
or
SELECT FROM table_name
WHERE column LIKE ‘_YYYY’
or
SELECT FROM table_name
WHERE column LIKE ‘_YYYY_’
You can combine N number of conditions using AND or OR operators. Here, YYYY could be any numeric or string value.
Here are number of examples showing WHERE part having different LIKE clause with ‘%’ and ‘_’ operators
| Statement | Description |
|---|---|
| WHERE MARK LIKE ‘500%’ | Finds any values that start with 500 |
| WHERE MARK LIKE ‘%500%’ | Finds any values that have 500 in any position |
| WHERE MARK LIKE ‘_00%’ | Finds any values that have 00 in the second and third positions |
| WHERE MARK LIKE ‘5_%_%’ | Finds any values that start with 5 and are at least 3 characters in length |
| WHERE MARK LIKE ‘%5’ | Finds any values that end with 5 |
| WHERE MARK LIKE ‘_2%3’ | Finds any values that have a 2 in the second position and end with a 3 |
| WHERE MARK LIKE ‘2___3’ | Finds any values in a five-digit number that start with 2 and end with 3 |
Example of LIKE clause
Consider Employee table
| Employee_ID | Name | Salary |
|---|---|---|
| 101 | Adam | 15000 |
| 102 | Alex | 18000 |
| 103 | Abhi | 17000 |
Example 1: SELECT * from Employee where Name like ‘A%’;
The above query will return all records where Name starts with character ‘A’ from Employee table.
| Employee_ID | Name | Salary |
|---|---|---|
| 101 | Adam | 15000 |
| 102 | Alex | 18000 |
| 103 | Abhi | 17000 |
Example2: SELECT * from Employee where Name like ‘_d%’;
The above query will return all records from Employee table where Name contain ‘d’ as second character.
| Employee_ID | Name | Salary |
|---|---|---|
| 101 | Adam | 15000 |
Example 3: SELECT * from Employee where Name like ‘%x’;
The above query will return all records from Employee table where Name contain ‘x’ as last character.
| Employee_ID | Name | Salary |
|---|---|---|
| 102 | Alex | 18000 |
Example4: SELECT * from Employee where Name like ‘%d%’;
The above query will return all records from Employee table where Name contain ‘d’ as second character.
| Employee_ID | Name | Salary |
|---|---|---|
| 101 | Adam | 15000 |
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.