SQL LIKE Operator
SQL LIKE operator is used to compare a value to similar values using wildcard operators.
There are two wildcards:
- % (Percent Symbol) – It indicates sequence of 0 or more characters or numbers.
- _ (Underscore Symbol) – It indicates a single character or number.
- A% : First Letter is ‘A’ followed by any number of characters.
- %A : Last letter is ‘A’ preadded by 0 or more characters.
- %A% : Character ‘A’ may appear anywhere in the word.
- %ANK% : A sequence ‘ANK’ may appear anywhere in the word.
- A_ _ _ _ : A five letter word starting with ‘A’.
Queries on LIKE Operator (using % and _ Wildcards) :
- Find the student details whose name starts with ‘S’ and from branch CSE.
SELECT * FROM Student WHERE Name LIKE 'S%' AND Branch = 'CSE';
- Find all the student details who opted ‘network protocol’ as a subject.
SELECT * FROM Student WHERE Subject LIKE '%network protocol%';
- Find the details of student whose 1st letter is ‘A’ and 4th letter is C and last letter is ‘D’.
SELECT * FROM Student WHERE Name LIKE 'A_ _ C%D';
- Find the details of students who are in ‘CSE’ branch and coming from Hyderabad and whose 3rd letter is ‘A’.
SELECT * FROM Student WHERE Name LIKE '_ _ A%' AND City = 'Hyderabad' AND Branch = 'CSE';
Searching Special Character Using Escape Symbol (/) :
The escape Symbol is used when we want to search some special characters like %(percent symbol) or an _ (Underscore Symbol) in the data.
For this, we have to put the escape symbol before the special characters.
For Example :
Find the details of the order, whose ordername starts with ‘P’ and ends with ‘S’ and contains a ‘%’ symbol?
SELECT * FROM Orderdetails WHERE Ordername LIKE 'P% /% %S';
|Operators in SQL with Example||Queries on SQL Operators|
Incoming search terms:
- %a in dbms
- like operator in dbms