Oracle SQL LIKE Operator with Examples
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:
