LIKE Operator

             The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. You May get some questions on this topic in interviews.

In Oracle, % (percentage) and _ (underscore) have a special meaning. These two wild card characters are used with LIKE operator: 

  % allows matching any string of any length (including zero length), _ allows matching a single character.

Example #1

SELECT dname FROM dept  WHERE dname LIKE 'A%';

                 will return all Department names starting with A like ACCOUNTING.
Output:
DNAME
————-
ACCOUNTING
Example #2
In the above statement, if we change A% to %S i.e.

SELECT dname FROM dept  WHERE dname LIKE  '%S';

                     The result would only return the dnames which are end with S i.e.
Output:
DNAME
————-
OPERATIONS
SALES
                                         While searching for a string in Oracle, sometimes one might want to use % and _ literally. i.e. there could be cases when you’d like to do a start with search using an input/search string containing % or _.
                                   The expected output should return only those strings that actually contain these character(s) at the specified places and not make use of the “special meaning” attached to them .
This can be obtained by escaping the wild card characters by defining an escape character:
For this let us assume that we have a table called student and that table have the data like below mentioned.
SNO          MAIL
1                ravikumar.pasupula%gmail.com
2                ravikumar.pasupula@gmail.com
3                ravikumar.pasupula_gmail.com
Example #3:

SELECT mail FROM student  WHERE mail LIKE '%\%%' ESCAPE  '\';

Output:
MAIL
————-
ravikumar%gmail.com
Example #4:

SELECT mail FROM student  WHERE mail LIKE '%\_%' ESCAPE  '\';

Output:
MAIL
————-
ravikumar_gmail.com