SQL LIKE Operator



SQL LIKE Operator :

  1. The LIKE operator is used in a WHERE clause.
  2. The LIKE operator is used to search for a specified pattern in a column.

Syntax :

Syntax for the LIKE Operator is as below –

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Demo Database :

Consider the following demo table –

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Omkar    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Example Pattern #1 :

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE 'r%';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
+----+----------+-----+-----------+----------+

In the above example using above query, we have selected only records that are staring with character ‘r’.

Example Pattern #2 :

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE '%h';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

In the above example using above query, we have selected only records that are ending with character ‘h’.

Example Pattern #3 :

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE '%a%';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Omkar    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

In the above example using above query, we have selected only records that are staring and ending with any characters but having character ‘a’ anywhere in the middle.