Hi,
I will explain SQL Like Operator in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
SQL SELECT Statement and DESC Command in Oracle SQL | Oracle SQL Tutorials -2
The LIKE operator is very popular operator in Oracle SQL that is used in a WHERE clause to search for a specified pattern in a column.
There are two types of wildcards used with the LIKE operator as follows.
- _ ( underscore ) represents matching only a single character.
- % ( Percent Sign ) represents matching one or multiple characters in Where clause.
LIKE Syntax
The LIKE Syntax is as follows.
SELECT * FROM table_name WHERE column LIKE pattern;
SELECT column1, column2, …FROM table_name
WHERE column LIKE pattern;
Like operator and its usage with examples are as follows.
| LIKE Operator | Description |
| WHERE FirstName LIKE ‘e%’ | Finds any values that start with “e” |
| WHERE FirstName LIKE ‘%e’ | Finds any values that end with “e” |
| WHERE FirstName LIKE ‘%msd%’ | Finds any values that have “msd” in any position |
| WHERE FirstName LIKE ‘_b%’ | Finds any values that have “b” in the second position |
| WHERE FirstName LIKE ‘u_%’ | Finds any values that start with “u” and are at least 2 characters in length |
| WHERE FirstName LIKE ‘u__%’ | Finds any values that start with “u” and are at least 3 characters in length |
| WHERE FirstName LIKE ‘u%a’ | Finds any values that start with “u” and ends with “a” |
Find the employees that first name start with a character.
SQL> select first_name,last_name,salary from hr.employees where first_name like 'a%';
no rows selected
SQL>
Find the employees that first name start with A character.
SQL>
SQL> select first_name,last_name,salary from hr.employees where first_name like 'A%';
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Amit Banda 7200
Alexis Bull 5100
Anthony Cabrio 4000
Alberto Errazuriz 13000
Adam Fripp 9200
Alexander Hunold 10000
Alyssa Hutton 9800
Alexander Khoo 4100
Allan McEwen 10000
Alana Walsh 4100
10 rows selected.
SQL>
Find the employees that first name end with a character.
SQL> select first_name,last_name,salary from hr.employees where first_name like '%a'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Laura Bissot 4300 Julia Dellinger 4400 Alyssa Hutton 9800 Neena Kochhar 23000 Sundita Kumar 7100 Diana Lorentz 5200 Mattea Marvins 8200 Julia Nayer 4200 Lisa Ozer 12500 Joshua Patel 3500 Trenna Rajs 4500 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Nandita Sarchand 5200 Martha Sullivan 3500 Clara Vishney 11500 Shanta Vollman 7500 Alana Walsh 4100 16 rows selected. SQL>
Find the employees that first name have “ar” in any position as follows.
SQL> select first_name,last_name,salary from hr.employees where first_name like '%ar%'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Sundar Ande 7400 Sarah Bell 5000 Harrison Bloom 11000 Karen Colmenares 3500 Girard Geoni 3800 Charles Johnson 7200 Karen Partners 14500 Sarath Sewall 8000 Martha Sullivan 3500 Clara Vishney 11500 10 rows selected. SQL>
Find the employees that last name have “e” in the second position
SQL> SQL> select first_name,last_name,salary from hr.employees where last_name like '_e%'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Lex De Haan 23000 Matthew Weiss 9000 Ki Gee 3400 John Seo 3700 David Bernstein 10500 Sarath Sewall 8000 David Lee 7800 Girard Geoni 3800 Julia Dellinger 4400 Randall Perkins 3500 Sarah Bell 5000 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Kevin Feeney 4000 12 rows selected. SQL>
Find the employees that last name start with “B” and ends with “l”
SQL> select first_name,last_name,salary from hr.employees where last_name like 'B%l'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Sarah Bell 5000 Alexis Bull 5100 SQL>
Find the employees that last name start with “B” and are at least 3 characters in length
SQL> select first_name,last_name,salary from hr.employees where last_name like 'B__%'; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Hermann Baer 11000 Shelli Baida 3900 Amit Banda 7200 Elizabeth Bates 8300 Sarah Bell 5000 David Bernstein 10500 Laura Bissot 4300 Harrison Bloom 11000 Alexis Bull 5100 9 rows selected. SQL>
fwe
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )