Using Regular Expressions in Oracle Database 10g

Using Basic Searches

Using Multilingual Capabilities

Regular Expressions and Check Constraints

Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial

OR

Completed the Installing Oracle Database 10g on Linux tutorial

Regular Expression: 'a(b|c)d'
String to Match:    'aabcd'

*

Matches zero or more occurrences

|

Alternation operator for specifying alternative matches

^/$
     

Matches the start of line and the end of line

[]

Bracket expression for a matching list matching any one of the expressions represented in the list

[^exp]

If the caret is inside the bracket, it negates the expression.

{m}

Matches exactly m times

{m,n}

Matches at least m times but no more than n times

[: :]

Specifies a character class and matches any character in that class

\

Can have four different meanings: (1) stand for itself; (2) quote the next character; (3) introduce an operator; (4) do nothing

+

Matches one or more occurrences

?

Matches zero or one occurrence

.

Matches any character in the supported character set (except NULL)

()

Grouping expression (treated as a single subexpression)

\n

Backreference expression

[==]
     

Specifies equivalence classes

[..]
     

Specifies one collation element (such as a multicharacter element)

In a terminal window, change to the /home/oracle/wkdir directory and start SQL*Plus.

Connect to Oracle with user ID oe/oe and password oe/oe.

cd /home/oracle/wkdir
sqlplus oe/oe

Examine the syntax of the REGEXP_LIKE function:

  REGEXP_LIKE(srcstr, pattern [,match_option]) 

In this function:

srcstr: Search value
pattern: Regular expression
match_option: Option to change default matching. Can include one or more of the following values:

'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line

To locate all products with names containing SSP/S, SSP/V, SSS/V, or SSS/S in the PRODUCT_NAME column from the PRODUCT_INFORMATION table, execute the following script:

@relike.sql

The relike.sql script contains the following SQL:

SELECT product_name
  FROM oe.product_information
  WHERE regexp_like (product_name, 'SS[PS]/[VS]');

The REGEXP_INSTR function returns the position of a given pattern within a string. Examine the syntax:

  REGEXP_INSTR(srcstr, pattern [, position [, occurrence 
               [, return_option [, match_option]]]]) 

In this function:

position: Search starting position
occurrence: Occurrence to search for
return_option: Indicate the start or end position of occurrence
match_option: Option to change default matching. Can include one or more of the following values:

'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line

To search the product names to find the location of the first nonalphabetic character (regardless of whether it is uppercase or lowercase), execute the following script:

@reinstr.sql

The reinstr.sql script contains the following SQL:

COLUMN non_alpha FORMAT 9999999999
SELECT product_name, REGEXP_INSTR(product_name, '[^[:alpha:]]') non_alpha
   FROM   oe.product_information ;

Note that [^[:<class>:]] implies a character class and matches any character from within that class; [:alpha:] matches any alphabetic character. In this case, you are negating this expression by using ^.

The REGEXP_SUBSTR function returns a given string based on a pattern of occurrence. Examine the syntax:

  REGEXP_SUBSTR(srcstr, pattern [, position 
    [, occurrence [, match_option]]]) 

In this function:

position: Search starting position
occurrence: Occurrence to search for
match_option: Option to change default matching. Can include one or more of the following values:

'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line

To extract the e-mail names from the CUSTOMERS table, extract only those e-mail names for customers located in Switzerland. To do this, return the contents in the CUST_EMAIL column that precedes the @ symbol for customers with an NLS_TERRITORY of Switzerland. Execute the following script:

@resubstr.sql

The resubstr.sql script contains the following SQL:

SELECT REGEXP_SUBSTR(cust_email, '[^@]+')
  FROM   oe.customers
  WHERE  nls_territory = 'SWITZERLAND' ; 

Note that in this example, the result returns the first substring that does not have the @ symbol.

The REGEXP_REPLACE function returns a "replaced" substring from a given string. Examine the syntax:

  REGEXP_REPLACE(srcstr, pattern [,replacestr [, position 
                 [, occurrence [, match_option]]]]) 

In this function:

position: Search starting position
occurrence: Occurrence to search for
replacestr: Character string replacing pattern
match_option: Option to change default matching. Can include one or more of the following values:

'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line

To return information from the CATALOG_URL column in the PRODUCT_INFORMATION table, you can perform a full scan on the column. But this would result in hundreds of rows being returned, because it lists a specific HTML page location within a number of catalog domains. However, in this example, you want to find only the individual domain names themselves and not the lower-level pages they contain. To find the domain names without all the extraneous information, use the REGEXP_REPLACE function. Execute the following script:

@rereplace.sql

The rereplace.sql script contains the following SQL:

SELECT UNIQUE REGEXP_REPLACE (catalog_url, 'http://([^/]+).*', '\1')
  FROM oe.product_information ;

Here is an explanation of how the string was processed:

Execute the following script to find the product description in the Portuguese language:

@multiport.sql

The multiport.sql script contains the following SQL:

SELECT regexp_substr(to_char(translated_name), '^[a-z]+')
FROM   oe.product_descriptions
WHERE  language_id = 'PT'
AND    translated_name like 'G%' ;

Note that the data is not displayed.

The ^ is outside the bracket, which means that you are searching for any strings or substrings that start with any character from a to z.

Perform the same query, but this time use the case-insensitive 'i' switched on. Execute the following script:

@multiport2.sql

The multiport2.sql script contains the following SQL:

SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')
FROM   oe.product_descriptions
WHERE  language_id = 'PT'
AND    translated_name like 'G%' ;

The results are still incomplete because the returned strings are trimmed as soon as a non-English character is encountered. This is because the range [a-z] is sensitive to NLS_LANGUAGE. You thus need to set the NLS_LANGUAGE parameter appropriately to return the complete results. Execute the following query:

@multiport3.sql

The multiport3.sql script contains the following SQL:

ALTER SESSION SET NLS_LANGUAGE=PORTUGUESE;
SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')
FROM   oe.product_descriptions
WHERE  language_id = 'PT'
AND    translated_name like 'G%' ;

The final step is to view the results in both English and Portuguese to ensure that the translation has taken place. Execute the following script:

@multiport4.sql

The multiport4.sql script contains the following SQL:

SELECT REGEXP_SUBSTR(i.product_name, '^[a-z]+', 1, 1, 'i') || ' = '
       || regexp_substr(to_char(d.translated_name), '^[a-z]+', 1, 1, 'i')
FROM   oe.product_descriptions d, oe.product_information i
WHERE  d.language_id = 'PT'
AND    d.translated_name like 'G%'
AND    i.product_id = d.product_id ;

ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

Adding a check constraint on the CUST_EMAIL column of the CUSTOMERS table ensures that only strings containing an @ symbol are accepted. Execute the following script:

@chkemail.sql

The chkemail.sql script contains the following SQL:

INSERT INTO customers VALUES 
           (9999,'Christian','Patel',
            cust_address_typ ('1003 Canyon Road','87501',
            'Santa Fe','NM','US'),
            phone_list_typ ('+1 505 243 4144'),'us','AMERICA','100',
            'ChrisP+creme.com', 149, null, null, null, null, null) ; 

Because there was no validation being performed, an e-mail address not containing an @ symbol was accepted. Perform a rollback before starting the next step.

ROLLBACK ;

Implement the constraint by executing the following script:

@chkemail2.sql

The chkemail2.sql script contains the following SQL:

ALTER TABLE customers 
  ADD CONSTRAINT cust_email_addr 
      CHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ;

Test the constraint by executing @chkemail.sql again.

The check constraint is violated because the e-mail address does not contain the required symbol. The NOVALIDATE clause ensures that existing data is not checked.

Remove the constraint by executing the following script:

@chkemail3.sql

The chkemail3.sql script contains the following SQL:

ALTER TABLE customers DROP CONSTRAINT cust_email_addr ;