LIKE in DBMS
LIKE in DBMS
In this article, we will learn about LIKE in DBMS.
Suppose you need to search some records based on certain patterns from the database tables then we will make use of this
- If you want to search all employees starting with letter P or names of all products which which consists of exactly 4 letters etc
- LIKE in DBMS operator used to search specified pattern in the data and retrieve the record when there is a pattern match as required
General Syntax for LIKE
SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;
% : Percent(%)represents 0,1 or multiple characters
_ : Underscore(_) is used to match exactly a single character
This wild card operator is used in conjunction with where clause and filter the records based on specified patterns as required.
Different ways that we can use this LIKE clause
The following are the rules for pattern matching with the LIKE Clause:
| PATTERN ||MEANING|
|‘r%’||Matches strings which start with ‘r’|
|‘%r’||Matches strings with end with ‘r’|
|‘r%t’||Matches strings which contain the start with ‘r’ and end with ‘t’.|
|‘%tri%’||Matches strings which contain the substring ‘tri’ in them at any position.|
|‘_tri%’||Matches strings which contain the substring ‘tri’ in them at the second position.|
|‘_r%’||Matches strings which contain ‘r’ at the second position.|
|‘r_%_%’||Matches strings which start with ‘r’ and contain at least 2 more characters.|
Sample table EMP
Display the employees whose name start with ‘M’
select ename from emp where ename like 'M%';
2 rows selected.
All employee names that start with letter M are displayed.
Display the names of all employees having M in any position in their name
select ename from emp where ename like '%M%';
5 rows selected.
The above query discuss the employee name that contains one or more M anywhere in their names.
Display the names of employees whose name contain the second letter as L
select ename from emp where ename like '_L%';
3 rows selected.
All string second letter as L are displayed.
Display the names of employees which contains the fourth letter as M
select ename from emp where ename like '___M%';
1 ROW SELECTED
In the above query, we have used 3 underscores(___) followed by letter M which mean that fourth letter should be M, like this, you can search for a specific character at any position based upon the number of underscores(_).
Display the employee names and hire dates for the employees joined in the month of December
select ename,hiredate from emp where hiredate LIKE '%DEC%';
3 ROWS SELECTED
For all the values of hire date wherever it found a string with ‘DEC’, all those records are displayed.
Display names of all employees whose name contains exactly 4 letters
select ename from emp where ename like '____';
3 rows selected
All employee names which contains four letters are displayed. Here we have used 4 underscores, you can display required length strings based on number of underscores (_).
Display the names of all employees whose name does not contain ‘A’ anywhere
select ename from emp where ename not like '%A%';
7 rows selected
Here all the names that are free from letter ‘A’ displayed