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 LIKE  operator

  • 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
LIKE in DBMS

General Syntax for LIKE

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

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

ENAMEHIREDATE
KING17-NOV-81
BLAKE01-MAY-81
CLARK09-JUN-81
JONES02-APR-81
SCOTT19-APR-87
FORD03-DEC-81
SMITH17-DEC-80
ALLEN20-FEB-81
WARD22-FEB-81
MARTIN28-SEP-81
TURNER08-SEP-81
ADAMS23-MAY-87
JAMES03-DEC-81
MILLER23-JAN-82

 

 

 Display the employees whose name start with ‘M’

select ename  from emp 
where ename like 'M%';

O/P

2 rows selected.
ENAME
MARTIN
MILLER

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.
ENAME
BLAKE
CLARK
ALLEN

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
ENAME
ADAMS

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
ENAMEHIREDATE
FORD03-DEC-81
SMITH17-DEC-80
JAMES03-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
ENAME
KING
FORD
WARD

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