Like Operator in SQL Server

Like Operator in SQL Server

•  By using the Like operator we are able to find out specific patterns in a column.

•  We use mostly it with Where clause

• To search pattern, Like operator use wildcard characters
  
  

Wildcard characters

insert into employee1 values(13,'Ratan','Pingali','Akola','Admin',23000)

insert into employee1 values(14,'Ramesh','Pilania','Akola','IT',50000)

insert into employee1 values(15,'Sunil','Pasalkar','Akola','HR',34000)

% - Represents zero or more characters

select * from employee1 where LastName like '%'

select * from employee1 where LastName like 'p%'

select * from employee1 where LastName like 'pa%'

select * from employee1 where LastName like 'shar%'

select * from employee1 where LastName like '%r'

_(underscore) -  represents single character

select * from employee1 where LastName like 's_a'

select * from employee1 where LastName like 's____a'

select * from employee1 where LastName like 'p___l'

select * from employee1 where LastName like 'p_t_l'

[] - Represents any single characters within the brackets

select * from employee1 where LastName like '[PS]'

After instert the data it will show

insert into employee1 values(16,'S','P','Akola','HR',40000)

delete from employee1 where EID = 16

select * from employee1 where LastName  like '[PS]%'

^ Represents any characters not in the brackets

select * from employee1 where LastName  like '[^PS]%'

- Represents any single characters within the specified range

select * from employee1 where LastName  like 'p'

select * from employee1 where LastName  like '[a-o]%'

Not LIKE Operator

select * from employee1 where LastName  not like 'p'

select * from employee1 where LastName  not like '[a-o]%'

select * from employee1 where LastName   like '[^a-o]%'

Some Practical Example

-Display the name whose third leter starts with r

select*from employee1 where FirstName like'__r%'

-Display the name which starts with s and ends with A

select * from employee1 where FirstName like's%a'