SQL SERVER Topic

LIKE Operator IN SQL
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. The LIKE operator is used to search for a specified pattern in a column.

Syntax :- 
  

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern


We can search records several ways by LIKE OPERATORS :-

  1. Starting Character :-If we want to search records starting with a paticular character or characters such as 'sa', 'a' ect. For this we write the following syntax :- 
      

SELECT * FROM table_name WHERE column_name LIKE 'sa%';  
    
    
----->>>'sa' is starting characters. We can use any characters, it depends on our requirements.


  2. Ending Character :-If we want to search records ending with a paticular character or characters such as 'ar', 'r' ect. For this we write the following syntax :- 
      

SELECT * FROM table_name WHERE column_name LIKE '%r';  
    
    
----->>>'r' is ending character. We can use any characters, it depends on our requirements.


  3. Pattern :-If we want to search records only match patter such as 'kumar'. For this we write the following syntax :- 
        
SELECT * FROM table_name WHERE column_name LIKE '%kumar%';
    
----->>>'kumar' is patter characters. We can use any characters, it depends on our requirements.


Example :- We create the following a table named is tblStudentRecord and inserts some records as shown below:-

  

CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)


Insert some records :-

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')
INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1006,'CSE','santosh@gmail.com','A-3031')
INSERT INTO tblStudentRecord VALUES('Neha sharma',1007,'MCA','h@gmail.com','D-34,Patna')
INSERT INTO tblStudentRecord VALUES('Pooja',1008,'ECE','pr@gmail.com','A-90, Jaipur, Rajashthan')
INSERT INTO tblStudentRecord VALUES('Jaya Laxmi',1009,'CSE','jl@gmail.com','Kerla')
INSERT INTO tblStudentRecord VALUES('Pakaj Kumar',1010,'BCA','pk@gmail.com','A-30,Darbhanga,Bihar')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1011,'ME','ab1@gmail.com','K-837')
INSERT INTO tblStudentRecord VALUES('Suneeta',1012,'MCA','su@gmail.com','UP')
INSERT INTO tblStudentRecord VALUES('Suman Kumar',1013,'IT','sk@gmail.com','Block-T,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Ajay kumar',1014,'ECE','aj@gmail.com','D-89')




Now, we use LIKE OPERATOR for all three conditions shown as below:-

1. Display record that start with 's' 

  
SELECT * FROM tblStudentRecord WHERE Name LIKE 's%'




2. Display record that ending with 'r' 

  
SELECT * FROM tblStudentRecord WHERE Name LIKE '%r'






3. Display record that pattern is 'kumar' 

  
SELECT * FROM tblStudentRecord WHERE Name LIKE '%kumar%'