LIKE Clause in SQL

Like clause is used for fetching similar values from table(s). For e.g. you may want to fetch all the names from a table that starts with alphabet “A” and ends with alphabet “X”, in such case you can use like clause in SQL query. In this tutorial we will see variations and use of Like clause in SQL queries.

Syntax:

SQL> SELECT column_name1, column_name2....
FROM TableName
WHERE [condition] 
LIKE Expression;

Expression can contain percentage sign(%) or underscore(_) or both.
Here percentage sign(%) represents a string of character having zero or more characters and underscore(_) refers a single character. It may sound confusing, lets take few examples to understand them better:

Lets say I have a table “STUDENT” with a field(column) called STUDENT_NAME.

1) Find all the student names that start with character “A”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%';

2) Find all the student names that start with character “X” and end with char “Z”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%Z';

3) Find student names having 2nd and 4th character as “C” and “A” respectively:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_C_A%';

4) Find student names that end with char “B” and have only 4 characters in name:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_ _ _B';

5) Find student names that have a “E” at the second position and end with “Y”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_E%Y';

Complete Example

STUDENT table:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

Find all the records from the above table that have CITY names starting with char “D”.

SQL> SELECT NAME FROM STUDENT
WHERE CITY LIKE 'D%';

Result:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
+---------+----------+-----+-----------+----------+