Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting007.htm

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting007.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-04-21T9:46:23Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Specifying Conditions with Regular Expressions" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10766-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdddg_selecting006.htm" title="Previous" type="text/html" />
<link rel="next" href="tdddg_selecting008.htm" title="Next" type="text/html" />
<title>Specifying Conditions with Regular Expressions</title>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdddg_selecting006.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting008.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="BCGFAAIH" name="BCGFAAIH"></a><a id="TDDDG22300" name="TDDDG22300"></a></p>
<div class="sect1">
<h1>Specifying Conditions with Regular Expressions</h1>
<a name="BEGIN" id="BEGIN"></a>
<p><a id="sthref109" name="sthref109"></a><a id="sthref110" name="sthref110"></a>As stated in <a href="tdddg_selecting006.htm#BCGBGBDC">"Selecting Data that Satisfies Specified Conditions"</a>, the condition in the <code>WHERE</code> clause can be any SQL condition. This topic shows how to specify conditions with SQL functions that accept <a id="sthref111" name="sthref111"></a>regular expressions. A regular expression defines a search <a id="sthref112" name="sthref112"></a>pattern, using metacharacters to specify search algorithms and literals to specify characters.</p>
<p>Suppose that you want to select all managers in the <code>EMPLOYEES</code> table. The <code>JOB_ID</code> of a manager ends with either <code>'_MGR'</code> or <code>'_MAN'</code>, depending on the department. Therefore, the search pattern must be a regular expression, and you must use the <code>REGEXP_LIKE</code> function, as in the following example.</p>
<p>In the regular expression <code>(_m[an|gr])</code>, the metacharacter <code>|</code> indicates the <code>OR</code> condition. The third function parameter, <code>'i'</code>, specifies that the match is case-insensitive.</p>
<div class="example"><a id="CEGEIGHH" name="CEGEIGHH"></a><a id="TDDDG137" name="TDDDG137"></a>
<p class="titleinexample">Selecting All Managers in the EMPLOYEES Table</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE <span class="bold"><a id="sthref113" name="sthref113"></a>REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i')</span>;
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
Michael              Hartstein                 MK_MAN
Shelley              Higgins                   AC_MGR
Nancy                Greenberg                 FI_MGR
Den                  Raphaely                  PU_MAN
Matthew              Weiss                     ST_MAN
Adam                 Fripp                     ST_MAN
Payam                Kaufling                  ST_MAN
Shanta               Vollman                   ST_MAN
Kevin                Mourgos                   ST_MAN
John                 Russell                   SA_MAN
Karen                Partners                  SA_MAN
 
FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
Alberto              Errazuriz                 SA_MAN
Gerald               Cambrault                 SA_MAN
Eleni                Zlotkey                   SA_MAN
 
14 rows selected.
</pre></div>

<p>Suppose that you want to select every employee whose last name has a double vowel (two adjacent occurrences of the same vowel). The following example shows how you can do this.</p>
<p>The regular expression <code>([AEIOU])</code> represents any vowel. The metacharacter <code>\1</code> represents the first (and in this case, only) regular expression. The third function parameter, <code>'i'</code>, specifies that the match is case-insensitive.</p>
<div class="example"><a id="CEGIGAHJ" name="CEGIGAHJ"></a><a id="TDDDG138" name="TDDDG138"></a>
<p class="titleinexample">Selecting All Employees Whose Last Names Have Double Vowels</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE <span class="bold"><a id="sthref114" name="sthref114"></a>REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i')</span>;
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
FIRST_NAME           LAST_NAME
-------------------- -------------------------
Harrison             Bloom
Lex                  De Haan
Kevin                Feeney
Ki                   Gee
Nancy                Greenberg
Danielle             Greene
Alexander            Khoo
David                Lee
 
8 rows selected.
</pre></div>

<p>Suppose that, in the displayed query results, you want to replace phone numbers that are stored in the format <code><span class="codeinlineitalic">nnn</span></code>.<code><span class="codeinlineitalic">nnn</span></code>.<code><span class="codeinlineitalic">nnnn</span></code> with their equivalents in the format <code>(</code><code><span class="codeinlineitalic">nnn</span></code><code>)</code> <code><span class="codeinlineitalic">nnn-nnnn</span></code>. You can use the <code>REGEXP_REPLACE</code> function, with regular expressions in the search pattern (the stored format) and references to those regular expressions in the replace string (the display format), as in the following example.</p>
<p>The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter <code>[[:digit:]]</code> represents a digit, the metacharacter <code>{</code><code><span class="codeinlineitalic">n</span></code><code>}</code> specifies <span class="italic">n</span> occurrences, and the metacharacter <code>\</code> is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter <code>.</code> represents any character.</p>
<p>The replace string uses <code>\1</code>, <code>\2</code>, and <code>\3</code> to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, <code>\</code> is not an escape character.)</p>
<div class="example"><a id="CEGBBJEC" name="CEGBBJEC"></a><a id="TDDDG139" name="TDDDG139"></a>
<p class="titleinexample">Displaying Phone Numbers in a Different Format</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT PHONE_NUMBER "Old Format",
<span class="bold"><a id="sthref115" name="sthref115"></a>REGEXP_REPLACE(PHONE_NUMBER,</span>
<span class="bold">'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',</span>
<span class="bold">'(\1) \2-\3')</span> "New Format"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
Old Format          New Format
-------------------- ---------------------------------------------------------
515.123.4567         (515) 123-4567
515.123.4568         (515) 123-4568
515.123.4569         (515) 123-4569
</pre></div>

<p>Suppose that you want to extract the street number from each <code>STREET_ADDRESS</code> in the <code>LOCATIONS</code> table. Some street numbers include hyphens, so the search pattern must use a regular expression, and you must use the <code>REGEXP_SUBSTR</code> function, as in the following example.</p>
<div class="example"><a id="CEGCAGAC" name="CEGCAGAC"></a><a id="TDDDG140" name="TDDDG140"></a>
<p class="titleinexample">Extracting the Street Number from Each STREET_ADDRESS</p>
<pre xml:space="preserve" class="oac_no_warn">
COLUMN Number FORMAT A8
SELECT STREET_ADDRESS "Address",
<span class="bold">REGEXP_SUBSTR(STREET_ADDRESS, '[[:digit:]-]+')</span> "Number"
FROM LOCATIONS;
COLUMN Number CLEAR
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
Address                                  Number
---------------------------------------- --------
1297 Via Cola di Rie                     1297
93091 Calle della Testa                  93091
2017 Shinjuku-ku                         2017
9450 Kamiya-cho                          9450
2014 Jabberwocky Rd                      2014
2011 Interiors Blvd                      2011
2007 Zagora St                           2007
2004 Charade Rd                          2004
147 Spadina Ave                          147
6092 Boxwood St                          6092
40-5-12 Laogianggen                      40-5-12
 
Address                                  Number
---------------------------------------- --------
1298 Vileparle (E)                       1298
12-98 Victoria Street                    12-98
198 Clementi North                       198
8204 Arthur St                           8204
Magdalen Centre, The Oxford Science Park
9702 Chester Road                        9702
Schwanthalerstr. 7031                    7031
Rua Frei Caneca 1360                     1360
20 Rue des Corps-Saints                  20
Murtenstrasse 921                        921
Pieter Breughelstraat 837                837
 
Address                                  Number
---------------------------------------- --------
Mariano Escobedo 9991                    9991
 
23 rows selected.
</pre></div>

<p>To count the number of spaces in each <code>STREET_ADDRESS</code>, you can use the <code>REGEXP_COUNT</code> function, as in the following example.</p>
<div class="example"><a id="CEGHDJEC" name="CEGHDJEC"></a><a id="TDDDG141" name="TDDDG141"></a>
<p class="titleinexample">Counting the Number of Spaces in Each STREET_ADDRESS</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT STREET_ADDRESS,
<span class="bold"><a id="sthref116" name="sthref116"></a>REGEXP_COUNT(STREET_ADDRESS, ' ')</span> "Number of Spaces"
FROM LOCATIONS;
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
1297 Via Cola di Rie                                    4
93091 Calle della Testa                                 3
2017 Shinjuku-ku                                        1
9450 Kamiya-cho                                         1
2014 Jabberwocky Rd                                     2
2011 Interiors Blvd                                     2
2007 Zagora St                                          2
2004 Charade Rd                                         2
147 Spadina Ave                                         2
6092 Boxwood St                                         2
40-5-12 Laogianggen                                     1
 
STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
1298 Vileparle (E)                                      2
12-98 Victoria Street                                   2
198 Clementi North                                      2
8204 Arthur St                                          2
Magdalen Centre, The Oxford Science Park                5
9702 Chester Road                                       2
Schwanthalerstr. 7031                                   1
Rua Frei Caneca 1360                                    4
20 Rue des Corps-Saints                                 3
Murtenstrasse 921                                       1
Pieter Breughelstraat 837                               2
 
STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
Mariano Escobedo 9991                                   2
 
23 rows selected.
</pre></div>

<p>To report the position of the first space in each <code>STREET_ADDRESS</code>, you can use the <code>REGEXP_INSTR</code> function, as in the following example.</p>
<div class="example"><a id="CEGHDGAB" name="CEGHDGAB"></a><a id="TDDDG142" name="TDDDG142"></a>
<p class="titleinexample">Reporting the Position of the First Space in Each STREET_ADDRESS</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT STREET_ADDRESS,
<span class="bold">REGEXP_INSTR(STREET_ADDRESS, ' ')</span> "First Space"
FROM LOCATIONS;
</pre>
<p>Result is similar to:</p>
<pre xml:space="preserve" class="oac_no_warn">
STREET_ADDRESS                           First Space
---------------------------------------- -----------
1297 Via Cola di Rie                               5
93091 Calle della Testa                            6
2017 Shinjuku-ku                                   5
9450 Kamiya-cho                                    5
2014 Jabberwocky Rd                                5
2011 Interiors Blvd                                5
2007 Zagora St                                     5
2004 Charade Rd                                    5
147 Spadina Ave                                    4
6092 Boxwood St                                    5
40-5-12 Laogianggen                                8
 
STREET_ADDRESS                           First Space
---------------------------------------- -----------
1298 Vileparle (E)                                 5
12-98 Victoria Street                              6
198 Clementi North                                 4
8204 Arthur St                                     5
Magdalen Centre, The Oxford Science Park           9
9702 Chester Road                                  5
Schwanthalerstr. 7031                             17
Rua Frei Caneca 1360                               4
20 Rue des Corps-Saints                            3
Murtenstrasse 921                                 14
Pieter Breughelstraat 837                          7
 
STREET_ADDRESS                           First Space
---------------------------------------- -----------
Mariano Escobedo 9991                              8
 
23 rows selected.
</pre></div>

<div class="helpinfonotealso">
<h2>Related Topics</h2>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=ADFNS1003','newWindow').focus()"><span class="italic">Oracle Database Advanced Application Developer's Guide</span></a></p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF020','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for syntax of regular expressions</p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF00501','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about the <code>REGEXP_LIKE</code> expression</p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06302','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about the <code>REGEXP_REPLACE</code> expression</p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06303','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about the <code>REGEXP_SUBSTR</code> expression</p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20014','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about the <code>REGEXP_COUNT</code> expression</p>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06300','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about the <code>REGEXP_INSTR</code> expression</p>
<p><a href="tdddg_selecting.htm#CHDHHAFA">Selecting Table Data</a></p>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdddg_selecting006.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting008.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de