LIKE in DBMS

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: Sample table EMP

 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.

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

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

Here all the names that are free from letter ‘A’ displayed