What is Like operator in SQL?

Like operator in SQL is a logical operator that matches and determines whether a character string matches a particular specified pattern.  Regular characters and wildcard characters are present in that pattern.

Like operator is often used with where and select keyword in a query.  

The pattern with which the values are to be compared is enclosed within single quotes. (‘ ‘ )

Wildcard characters are generally of the following types:

1. Underscore ( _ ): It can be used to specify a single character in a pattern.

For eg:  let us consider a query :

• select name from emp where name like ‘_a’
• explanation: This query will extract two-letter data from the column ‘name’ from table ‘emp’ by comparing the values in ‘name’ column with the pattern ‘_a’ which means there can be any single character value on the first position but it should only have ‘a’ exactly after it.

2. Percent sign (%): It can be used to specify one, two, or multiple characters in a pattern.

For eg:  let us consider a query :

• select name from emp where name like ‘a%’
• explanation: This query will extract data from the column ‘name’ from table ‘emp’ by comparing the values in ‘name’ column with the pattern ‘a%’ which means there can be any number of distinct characters after ‘a’ but it has to always begin with a.

Note: These wildcards can be arranged in any order or even in combination.

For a better understanding of like operator lets create a table and add some entries:

Create table emp

(

id int,

name varchar(20),

department varchar(20),

salary int

)

 insert into emp (id, name, department, salary) values (1, 'nisha', 111,30000)

 insert into emp (id, name, department, salary) values (2, 'neha', 222,35000)

 insert into emp (id, name, department, salary) values (3, 'sarika', 333,40000)

 insert into emp (id, name, department, salary) values (4, 'sagar', 111,45000)

 insert into emp (id, name, department, salary) values (5, 'preet', 333,35000)

 insert into emp (id, name, department, salary) values (6, 'nishant', 222,30000)

         

A table with name ‘emp’ has been created with 6 entries in it.

select * from emp (to see the table data)

The query for the like operator:

• select name from emp where name like ‘s%’

This query will find out all the names from the table starting with letter S irrespective of the later alphabets.

Some more examples:

select name from emp where name like ‘_i%’

select name from emp where name like ‘pr___’

Query result:

Similarly, if we want the complete row of the matching pattern the following query can be used:

Select * from emp where name like ‘pr___’

Query result: