Previous
Next
Previous |
Next |
As stated in "Selecting Data that Satisfies Specified Conditions", the condition in the WHERE clause can be any SQL condition. This topic shows how to specify conditions with SQL functions that accept regular expressions. A regular expression defines a search pattern, using metacharacters to specify search algorithms and literals to specify characters.
Suppose that you want to select all managers in the EMPLOYEES table. The JOB_ID of a manager ends with either '_MGR' or '_MAN', depending on the department. Therefore, the search pattern must be a regular expression, and you must use the REGEXP_LIKE function, as in the following example.
In the regular expression (_m[an|gr]), the metacharacter | indicates the OR condition. The third function parameter, 'i', specifies that the match is case-insensitive.
Selecting All Managers in the EMPLOYEES Table
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i');
Result is similar to:
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.
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.
The regular expression ([AEIOU]) represents any vowel. The metacharacter \1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.
Selecting All Employees Whose Last Names Have Double Vowels
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i');
Result is similar to:
FIRST_NAME LAST_NAME -------------------- ------------------------- Harrison Bloom Lex De Haan Kevin Feeney Ki Gee Nancy Greenberg Danielle Greene Alexander Khoo David Lee 8 rows selected.
Suppose that, in the displayed query results, you want to replace phone numbers that are stored in the format nnn.nnn.nnnn with their equivalents in the format (nnn) nnn-nnnn. You can use the REGEXP_REPLACE 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.
The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter [[:digit:]] represents a digit, the metacharacter {n} specifies n occurrences, and the metacharacter \ is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter . represents any character.
The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, \ is not an escape character.)
Displaying Phone Numbers in a Different Format
SELECT PHONE_NUMBER "Old Format", REGEXP_REPLACE(PHONE_NUMBER, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') "New Format" FROM EMPLOYEES WHERE DEPARTMENT_ID = 90;
Result is similar to:
Old Format New Format -------------------- --------------------------------------------------------- 515.123.4567 (515) 123-4567 515.123.4568 (515) 123-4568 515.123.4569 (515) 123-4569
Suppose that you want to extract the street number from each STREET_ADDRESS in the LOCATIONS table. Some street numbers include hyphens, so the search pattern must use a regular expression, and you must use the REGEXP_SUBSTR function, as in the following example.
Extracting the Street Number from Each STREET_ADDRESS
COLUMN Number FORMAT A8
SELECT STREET_ADDRESS "Address",
REGEXP_SUBSTR(STREET_ADDRESS, '[[:digit:]-]+') "Number"
FROM LOCATIONS;
COLUMN Number CLEAR
Result is similar to:
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.
To count the number of spaces in each STREET_ADDRESS, you can use the REGEXP_COUNT function, as in the following example.
Counting the Number of Spaces in Each STREET_ADDRESS
SELECT STREET_ADDRESS,
REGEXP_COUNT(STREET_ADDRESS, ' ') "Number of Spaces"
FROM LOCATIONS;
Result is similar to:
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.
To report the position of the first space in each STREET_ADDRESS, you can use the REGEXP_INSTR function, as in the following example.
Reporting the Position of the First Space in Each STREET_ADDRESS
SELECT STREET_ADDRESS,
REGEXP_INSTR(STREET_ADDRESS, ' ') "First Space"
FROM LOCATIONS;
Result is similar to:
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.
Oracle Database Advanced Application Developer's Guide
Oracle Database SQL Language Reference for syntax of regular expressions
Oracle Database SQL Language Reference for more information about the REGEXP_LIKE expression
Oracle Database SQL Language Reference for more information about the REGEXP_REPLACE expression
Oracle Database SQL Language Reference for more information about the REGEXP_SUBSTR expression
Oracle Database SQL Language Reference for more information about the REGEXP_COUNT expression
Oracle Database SQL Language Reference for more information about the REGEXP_INSTR expression
| Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
Previous |
Next |