Like in SQL

Written By:- Isha Malhotra

What is Like Operator in SQL

Like in SQL is very important and it is used to search or match the pattern. We use like operator with Where clause. It is used to filter rows on the basis of perticular pattern.

Wild card symbol in like operator

There are following type of wild card symbol in like operator: -

  • Percent (%) - it represents zero or any character
  • Underscore (_) - it represents one character
  • Square Brackets ([ ]) – use to display specific character given brackets. It will check pattern must contain the character given in brackets.

Check the data of following table: - 


		
records

		Figure 1

Percent (%) Wild Card Sign

Let’s say we need to select all data from table where name is starting from m. So we will write query in the following manner: - 


		
	select * from enqdata where name like 'm%'

% sign is checking the first character is m and after that any character. It will also select the data if name is having only m because % sign required only m in the beginning after that it doesn’t matter if name would contain any character or not.

now execute the code and you will get following output: - 


		
like operator start with

		Figure 2

Now let’s say I have to select those records which is having ‘a’ in the last of name. so we will write query in the following manner


		
select * from enqdata where name like '%a'

and you will get following output: - 


		
like operator end with

		Figure 3

Similarly, if we need to select data which is having sh in name so we will write query in the following manner 


		
	select * from enqdata where name like '%sh%'

now execute the code and you will get following output: - 


		
contains in like

		Figure 4

Underscore (_) Sign

Let’s say I have to select those records whose name is having ‘a’ as a second character. So we will write query in the following manner


		
select * from enqdata where name like '_a%'

and you will get following output: - 


		
underscore wild card

		Figure 5

Square Brackets ([ ])

Let’s say we need to select the data in which name is having second character either ‘e’ or ‘a’ 


		
select * from enqdata where name like '_[ae]%'

it will show the following output 


		
square brackets wild card

		Figure 6

We can also use negate (^) wild card. Let’s say we have to select data which name is having second character anything except ‘e’ or ‘a’. so we will write query in the following manner 


		
select * from enqdata where name like '_[^ae]%'

it will show the following output 


		
square brackets wild card with negate

		Figure 7

Not Like

We can also use not like query to select result. For example, if we need to select all data except name starting from m. then we will use query in the following manner 


		
select * from enqdata where name not like 'm%'

it will show all records except this condition.