IT Tutorial

SQL Like Operator in Oracle SQL | Oracle SQL Tutorials -12

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.

 


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 )

 1,134 views last month,  2 views today

Exit mobile version