Wildcard operators
% : 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
| ENAME |
HIREDATE |
| KING |
17-NOV-81 |
| BLAKE |
01-MAY-81 |
| CLARK |
09-JUN-81 |
| JONES |
02-APR-81 |
| SCOTT |
19-APR-87 |
| FORD |
03-DEC-81 |
| SMITH |
17-DEC-80 |
| ALLEN |
20-FEB-81 |
| WARD |
22-FEB-81 |
| MARTIN |
28-SEP-81 |
| TURNER |
08-SEP-81 |
| ADAMS |
23-MAY-87 |
| JAMES |
03-DEC-81 |
| MILLER |
23-JAN-82 |
Display the employees whose name start with ‘M’
select ename from emp
where ename like 'M%';
O/P
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%';
O/P
5 rows selected.
| ENAME |
| SMITH |
| MARTIN |
| ADAMS |
| JAMES |
| MILLER |
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%';
O/P
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%';
O/P
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%';
O/P
3 ROWS SELECTED
| ENAME |
HIREDATE |
| FORD |
03-DEC-81 |
| SMITH |
17-DEC-80 |
| JAMES |
03-DEC-81 |
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 '____';
O/P
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%';
O/P
7 rows selected
| ENAME |
| KING |
| JONES |
| SCOTT |
| FORD |
| SMITH |
| TURNER |
| MILLER |
Here all the names that are free from letter ‘A’ displayed
Login/Signup to comment