12 using regular expressions with oracle database

8/3/2019 12 Using Regular Expressions With Oracle Database

1/12

/11/12 12 Using Regular Epressions With Oracle Database

1/12ocs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

Pei Ne

Si Heade

Oracle Database Application Developer's Guide -

Fundamentals

10 Release 1 (10.1)

Par Nmber B10795-01 He B Li Ce Ide Mae Ide Feedbac

Vie PDF

12

Using Regular Epressions With Oracle

Database

Thi chae idce ega eei f Oace Daabae. Thi chae ce he fig

ic:

Wha ae Rega Eei?

Oace Daabae Rega Eei S

Oace Daabae SQL Fci f Rega Eei

Meachaace Sed i Rega Eei

Ccig Rega Eei

See Also:Oracle Database SQL Reference f addiia deai Oace Daabae

SQL fci f ega eei

Oracle Database Globaliation Support Guide f deai ig SQL

ega eei fci i a iiga eie

Mastering Regular Expressions bihed b O'Rei & Aciae, Ic.

What are Regular Epressions?

Rega eei ecif ae each f i ig daa ig adadied a cei. Aega eei ca ecif ce ae f chaace eece. F eae, he fig ega

eei:

a(bc)d

eache f he ae: 'a', fed b eihe 'b' 'c', he fed b 'd'. Thi ega eei ache

bh 'abd' ad 'acd'.

A ega eei i ecified ig e f chaace:

Meachaace--ea ha ecif agih f efig he each.

Liea--he aca chaace each f.


2/12



Eae f ega eei a ae gie ae i hi chae.

Oracle Database Regular Epression Support

Oace Daabae iee ega eei cia ih he POSIX Eeded Rega

Eei (ERE) ecificai.

Rega eei i ieeed ih a e f Oace Daabae SQL fci ha a each ad aiae ig daa. Y ca e hee fci i a eie hee Oace Daabae

SQL i ed. See "Oace Daabae SQL Fci f Rega Eei" ae i hi chae f e

ifai.

Oace Daabae a e f c eachaace ed i ega eei. The behai f

ed eachaace ad eaed feae i decibed i "Meachaace Sed i Rega

Eei".

Note:

The ieeai f eachaace diffe beee ha ega

eei i he id. If ae ig ega eei f ahe

eie Oace Daabae, ee ha he ega eei a i ed

ad he behai i ha eec.

Oracle Database SQL Functions for Regular EpressionsThe daabae ide a e f SQL fci ha a each ad aiae ig ig ega

eei. Y ca e hee fci a daae ha hd chaace daa ch a CHAR,

NCHAR, CLOB, NCLOB, NVARCHAR2, ad VARCHAR2.

A ega eei be eced aed beee ige e. Dig , ee ha he eie

eei i ieeed b he SQL fci ad ca ie he eadabii f cde.

Tabe 12-1 gie a bief decii f each ega eei fci.

Note:

A ih a e iea ed i SQL fci, ega eei be eced

aed beee ige e. If ega eei icde he ige e

chaace, ee ige ai a eee e ige ai a

ihi eei.

Table 12-1 QL Regular Epression Functions

SQL Fncion Decipion


3/12



REGEXP_LIKE Thi fci eache a chaace c f a ae. Ue hi fci i he

WHERE cae f a e e achig he ega eei

ecif.

See he Oacle Daabae SQL Refeence f a deai he REGEXP_LIKE

fci.

REGEXP_REPLACE Thi fci eache f a ae i a chaace c ad eace each

ccece f ha ae ih he ae ecif.

See he Oacle Daabae SQL Refeence f a deai he

REGEXP_REPLACE fci.

REGEXP_INSTR Thi fci eache a ig f a gie ccece f a ega eei ae.

Y ecif hich ccece a fid ad he a ii each f.

Thi fci e a iege idicaig he ii i he ig hee he ach i

fd.

See he Oacle Daabae SQL Refeence f a deai heREGEXP_INSTR fci.

REGEXP_SUBSTR Thi fci e he aca big achig he ega eei ae

ecif.

See he Oacle Daabae SQL Refeence f a deai he

REGEXP_SUBSTR fci.

Metacharacters Supported in Regular Epressions

Tabe 12-2 i he eachaace ed f e i ega eei aed SQL ega

eei fci. Deai he achig behai f hee eachaace i gie i "Ccig

Rega Eei".

Table 12-2 Metacharacters Supported in Regular Epressions

Metacharacter

Snta Operator Name Description

. A Chaace --

D

Mache a chaace

+ Oe Me --

P Qaifie

Mache e e ccece f he ecedig beei

? Ze Oe --

Qei Ma

Qaifie


* Ze Me --

Sa Qaifie


{ Iea--Eac

C

Mache eac ccece f he ecedig beei


4/12



m, Iea--A Lea

C

Mache a ea m ccece f he ecedig beei

m,n Iea--Beee

C

Mache a ea m, b e ha n ccece f he

ecedig beei

[ ... ] Machig

Chaace Li

Mache a chaace i i ...

[^ ... ] N-Machig

Chaace Li

Mache a chaace i i ...

O 'ab' ache chaace 'a' 'b'.

( ... ) Sbeei

Gig

Tea eei ... a a i. The beei ca be a ig f

iea a ce eei caiig ea.

\n Bacefeece Mache he h ecedig beei, hee n i a iege f

1 9.

\ Ecae Chaace Tea he bee eachaace i he eei a a iea.

^ Begiig f Lie

Ach

Mach he bee eei he i cc a he

begiig f a ie.

$ Ed f Lie

Ach

Mach he ecedig eei he i cc a he ed f a

ie.

[:cla:] POSIX Chaace

Ca

Mach a chaace begig he ecified chaacecla.

Ca be ed iide a i eei.

[.elemen.] POSIX Caig

Seece

Secifie a caig eece e i he ega eei. The

elemen e be a defied caig eece, i he

ce cae.

[=chaace=] POSIX Chaace

Eiaece Ca

Mach chaace haig he ae bae chaace a he chaace

ecif.

Constructing Regular Epressions

Thi eci dice cci f ega eei.

Basic String Matching ith Regular Epressions

The ie ach ha ca ef ih ega eei i he baic ig ach. F hi e f

ach, he ega eei i a ig f iea ih eachaace. F eae, fid he eece

'abc', ecif he ega eei:

abc

Regular Epression Operations on Subepressions

A eied eaie, ega eei ae cced ig eachaace ad iea. Meachaaceha eae a ige iea, ch a '+' ad '?' ca a eae a eece f iea a he

eei. T d , e he gig ea ece he eece beei. See

"Sbeei"f e ifai gig.


5/12



Regular Epression Operator and Metacharacter Usage

Thi eci gie age eae f each ed eachaace ega eei ea.

Match An Character--Dot

The do ea'.' ache a ige chaace i he ce chaace e. F eae, fid he

eece--'a', fed b a chaace, fed b 'c'- -e he eei:

.

Thi eei ache a f he fig eece:

1

&

The eei de ach:

One or More--Plus

The e e ea'+' ache e e ccece f he ecedig eei. F eae,

fid e e ccece f he chaace 'a', e he ega eei:

+

Thi eei ache a f he fig:

The eei de ach:

Zero or One--Question Mark Operator

The ei a ache e e--ad e--ccece f he ecedig chaace

beei. Y ca hi f hi ea a ecifig a eei ha i ia i he ce e.

F eae, fid--'a', ia fed b 'b', he fed b 'c'-- e he fig ega

eei:

?


6/12



Thi eei ache:

abc

ac

The eei de ach:

adc

abbc

Zero or More--Star

The e e ea'*', ache e e ccece f he ecedig chaace

beei. F eae, fid--'a', fed b e e ccece f 'b', he fed b 'c'--

e he ega eei:

ab*c


ac

abc

abbc

abbbbc

The eei de ach:

adc

Interval--Eact Count

The eac-c iea ea i ecified ih a ige digi eced i bace. Y e hi ea

each f a eac be f ccece f he ecedig chaace beei.

F eae, fid hee 'a' cc eac 5 ie, ecif he ega eei:

a{5

Thi eei ache:

aaaaa

The eei de ach:

aaaa


7/12



Ineal--A Lea Con

Y e he a-ea-c iea ea each f a ecified be f ccece, e, f he

ecedig chaace beei. F eae, fid hee 'a' cc a ea 3 ie, e he

ega eei:

a{3,

Thi eei ache a f he fig:

aaa

aaaaa

The eei de ach:

aa

Ineal--Beeen Con

Y e he beee-c iea ea each f a be f ccece ihi a ecified age.

F eae, fid hee 'a' cc a ea 3 ie ad e ha 5 ie, e he fig ega

eei:

a{3,5


aaa

aaaa

aaaaa

The eei de ach:

aa

Maching Chaace Li

Y e he achig chaace i each f a ccece f a chaace i a i. F eae, fid

eihe 'a', 'b', 'c' e he fig ega eei:

[abc]

Thi eei ache he fi chaace i each f he fig ig:

at

bet

cot


8/12



The eei de ach:

The fig ega eei ea ae aed ihi he chaace i, a he eachaace

icded i a chaace i e hei ecia eaig (ae eaed a iea):

Rage ea'-'POSIX chaace ca [::]

POSIX caig eece [. .]

POSIX chaace eiaece ca [= =]

Non-Maching Chaace Li

Ue he -achig chaace i ecif chaace ha d a ach. Chaace ha ae

i he -achig chaace i ae eed a a ach. F eae, ecde he chaace 'a', 'b',

ad 'c' f each e, e he fig ega eei:

[^]

Thi eei ache chaace 'd' ad 'g' i he fig ig:

The eei de ach:

A ih he achig chaace i, he fig ega eei ea ae aed ihi he -

achig chaace i (a he eachaace icded i a chaace i ae iged):

Rage ea'-'

POSIX chaace ca [::]

POSIX caig eece [. .]

POSIX chaace eiaece ca[= =]

F eae, he fig ega eei ecde a chaace beee 'a' ad 'i' f he each

e:

[^-]

Thi eei ache he chaace 'j' ad '' i he fig ig:

The eei de ach he chaace:


9/12



Or

Use the Or operator'' to specif an alternate epression. For eample to match 'a' or 'b', use the folloing

regular epression:

ab

Subepression

You can use the subepression operator to group characters that ou ant to find as a string or to create a

comple epression. For eample, to find the optional string 'abc', folloed b 'def', use the folloing regular

epression:

(abc)?def

This epression matches strings 'abcdef' and 'def' in the folloing strings:

abcdefghi

defghi

The epression does not match the string:

ghi

Backreference

The backreference lets ou search for a repeated epression. You specif a backreference ith '\', here

is an integer from 1 to 9 indicating the nth preceding subepression in our regular epression.

For eample, to find a repeated occurrence of either string 'abc' or 'def', use the folloing regular epression:

(abcdef)\1

This epression matches the folloing strings:

abcabc

defdef

The epression does not match the folloing strings:

abcdef

abc

The backreference counts subepressions from left to right starting ith the opening parenthesis of each

preceding subepression.


10/12



The backreference lets ou search for a repeated string ithout knoing the actual string ahead of time. For

eample, the regular epression:

^(.*)\1$

matches a line consisting of to adjacent appearances of the same string.

Ecape Chaace

Use the escape character'\' to search for a character that is normall treated as a metacharacter. For

eample to search for the '+' character, use the folloing regular epression:

\+

This epression matches the plus character '+' in the folloing string:

+

The epression does not match an characters in the string:

Beginning of Line Ancho

Use the beginning of line anchor^ to search for an epression that occurs onl at the beginning of a line. For

eample, to find an occurrence of the string at the beginning of a line, use the epression:

^

This epression matches in the string:

The epression does not match in the folloing string:

End of Line Ancho

The end of line anchor metacharacter'$' lets ou search for an epression that occurs onl at the end of a

line. For eample, to find an occurrence of that occurs at the end of a line, use the folloing epression:

$

This epression matches in the string:


11/12



The eei de ach def i he fig ig:

defghi

POSIX Chaace Cla

The POSIX chaace ca ea e each f a eei ihi a chaace i ha i a ebe

f a ecific POSIX Chaace Ca. Y ca e hi ea each f chaace ih ecific

faig ch a ecae chaace, ca each f ecia chaace ch a digi

cai chaace. The f e f POSIX chaace cae i ed.

T e hi ea, ecif he eei ig he a [:cla:] hee cla i he ae f he

POSIX chaace ca each f. F eae, each f e e cecie ecae

chaace, e he fig ega eei:

[[:pper:]]+

Thi eei ache 'DEF' i he ig:

abcDEFghi

The eei de e a ach f he fig ig:

abcdefghi

Ne ha he chaace ca cc ihi a chaace i, he chaace ca i aa eed ihi

he bace f he chaace i i he ega eei.

See Alo:

Mastering Regular Epressions bihed b O'Rei & Aciae, Ic. f e

ifai POSIX chaace cae

POSIX Collaing Seence

The POSIX caig eece eee ea[. .] e e a caig eece i ega

eei. The eee ecif be a defied caig eece i he ce cae.

Thi ea e e a ichaace caig eece i ega eei hee e

chaace d heie be aed. F eae, ca e hi ea ee ha he caig

eece 'ch', he defied i a cae ch a Saih, i eaed a e chaace i eai ha deed

he deig f chaace.

T e he caig eece ea, ecif [.elemen.] hee elemen i he caig eece

a fid. Y ca e a caig eece ha i defied i he ce cae icdig ige-chaace

eee a e a ichaace eee.


12/12


Previous Next

For example, to find the collating sequence 'ch', use the following regular expression:

[[.ch.]]

This expression matches the sequence 'ch' in the following string:

chabc

The expression does not match the following string:

cdefg

You can use the collating sequence operator in any regular expression where collation is needed. For

example, to specify the range from 'a' to 'ch', you can use the following expression:

[a-[.ch.]]

POSIX Chaace Eialence Cla

Use the POSIX character equivalence class operator to search for characters in the current locale that are

equivalent. For example, to find the Spanish character '' as well as 'n'.

To use this operator, specify [=characer=], to find all characters that are members of the same character

equivalence class as the specified characer.

For example, the following regular expression could be used to search for characters equivalent to 'n' in aSpanish locale:

[[=n=]]

This expression matches both 'N' and '' in the following string:

El Nio

Noe:

The character equivalence class must occur within a character list, so the

character equivalence class is always nested within the brackets for the character

list in the regular expression.

Usage of character equivalents depends on how canonical rules are defined for

your database locale. See the Oracle Database Globaliation Support Guide for

more information on linguistic sorting and string searching.

Copyright 1996, 2003 Oracle Corporation

All Rights Reserved.Home Book List Contents Index Master Index Feedback

12 using regular expressions with oracle database

Documents