Overview

The LIKE operator is used to filter data based on patterns. The operator is used in the WHERE clause. In the example section, we will look at various examples using the LIKE operator.

LIKE Operator

SELECT column_names FROM table_name

WHERE column_name LIKE ‘pattern’ ;

Wildcard characters in the pattern and the descriptions:

 

Wildcard	Description
_	_ The underscore wildcard matches any single character or single-digit
%	The % wildcard matches zero, or more characters or numbers
[ ]	The [] wildcard matches any single character in the specified range
[^]	The wildcard matches any single character except the specified range

 

Example

Let’s look at some examples to show the use of the LIKE operator.

For example, we want to know the employee no, name, and salary for employees whose names start with the letter M.

Example table data:

SQL> SELECT empno, ename, sal FROM employee;

 EMPNO ENAME SAL
---------- -------------------- ----------
 7369 SMITH 800
 7499 ALLEN 1600
 7521 WARD 1250
 7566 JONES 2975
 7654 MARTIN 1250

 

We can use the % wildcard in this example. The ‘M%’ matches names that start with M and any characters after the letter M.

SQL> SELECT empno, ename, sal FROM employee
WHERE ename LIKE ‘M%’;

# List employee names ending with the letter N.

Now we use the wildcard % at the beginning and the letter N as the end letters.

SQL> SELECT empno, ename, sal FROM employee
2         WHERE ename LIKE ‘%N’;

EMPNO     ENAME                   SAL
———-     ——————–   ———-
7499           ALLEN                     1600
7654           MARTIN                  1250

 

List employee names with ART in the name.

SQL> SELECT empno, ename, sal FROM employee
2         WHERE ename LIKE ‘%ART%’;

Now, we want to use the string ‘%art%’ in the LIKE operator. What change do we need to make the SQL query work?

—

Oracle Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/