Replacing a Data Pattern
Retrieving Data with Queries
2-12 Oracle Database 2 Day Developer's Guide
The results of the query appear.
First Last
-------------------- -------------------------
Harrison Bloom
Lex De Haan
Kevin Feeney
...
8 rows selected
To find a data pattern and replace it with another one, use the
REGEXPR_REPLACE
.
replaces the phone numbers of the format
'nnn.nnn.nnnn'
with the
format
'(nnn) nnn-nnnn'
. Note that digits are matched with the metacharacter
[:digit]
, while the metacharacter
{n}
indicates the number of occurrences. The
metacharacter
'.'
typically indicates any character in an expression, so the
metacharacter
\
is used as an escape character and makes the following character in
the pattern a literal. This result set shows the telephone numbers in the new format.
See Oracle Database SQL Language Reference for information about the
REGEXP_
REPLACE
condition.
Example 212 Replacing a Data Pattern
SELECT first_name "First", last_name "Last",
phone_number "Old Number",
REGEXP_REPLACE(phone_number,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Number"
FROM employees
WHERE department_id = 90;
The results of the query appear.
First Last Old Number New Number
-------------------- ---------------------- -------------------- --------------
Steven King 515.123.4567 (515) 123-4567
Neena Kochhar 515.123.4568 (515) 123-4568
Lex De Haan 515.123.4569 (515) 123-4569
3 rows selected
REGEXPR_SUBSTR
function to find the first
substring that matches a pattern. Note that the metacharacter'
+
' indicates multiple
occurrences of the pattern. This result set extracts numerals and dashes from the
street_address
column. See Oracle Database SQL Language Reference for information
about the
REGEXP_SUBSTR
expression.
Example 213 Returning a Substring
SELECT street_address, REGEXP_SUBSTR(street_address,
'[[:digit:]-]+', 1, 1) "Street Numbers"
FROM locations;
The results of the query appear.
STREET_ADDRESS Street Numbers
---------------------------------------- ----------------------
1297 Via Cola di Rie 1297
93091 Calle della Testa 93091
2017 Shinjuku-ku 2017
...
