background image
<< Testing for a Matching String | Replacing a Data Pattern >>
<< Testing for a Matching String | Replacing a Data Pattern >>

Searching for Patterns in Data

Retrieving Data with Queries
Querying and Manipulating Data 2-11
by the table name. However, because the
WHERE
clause uses the same column name
from two different tables, you must qualify it.
Example 2­9 Testing for a Value in Another Table
SELECT
e.
first_name "First",
e.
last_name "Last",
d.
department_name "Department"
FROM employees
e
, departments
d
WHERE
e.
department_id =
d
.department_id;
The results of the query appear.
First Last Department
-------------------- ------------------------- ------------------------------
Jennifer Whalen Administration
Michael Hartstein Marketing
Pat Fay Marketing
...
106 rows selected
Searching for Patterns in Data
Regular expressions
allow you to use standard syntax conventions to search for
complex patterns in character sequences. A regular expression defines the search
pattern by using metacharacters that specify search algorithms, and literals that
specify the characters.
Regular expression functions include
REGEXP_INSTR
,
REGEXP_LIKE
,
REGEXP_
REPLACE
, and
REGEXP_SUBSTR
.
Example 2­10
shows how to find all managers. The metacharacter
|
indicates an
OR
condition, which you must use because the manager position is specified either as '
%_
MGR
' or '
%_MAN
', depending on department. The option
i
specifies that the match
should be case insensitive.
Example 2­10 Finding a Matching Data Pattern
SELECT first_name "First", last_name "Last", job_id "Job"
FROM employees
WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i');
The results of the query appear.
First Last Job
-------------------- ------------------------- ----------
Nancy Greenberg FI_MGR
Den Raphaely PU_MAN
Matthew Weiss ST_MAN
...
14 rows selected
Example 2­11
shows how the
REGEXPR_LIKE
expression selects rows where the
last_name
has a double vowel (two adjacent occurrences of either
a
,
e
,
i
,
o
, or
u
).
See Oracle Database SQL Language Reference for information about the
REGEXP_LIKE
condition.
Example 2­11 Finding a Matching Data Pattern (Adjacent Characters)
SELECT first_name "First", last_name "Last"
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');