Appendix A. Regular Expression Metacharacters and Functions

If you have a background in Perl, you should be familiar with regular expressions added in version 10g of the Oracle data server. Oracle 10g supports the IEEE POSIX extended regular expressions standard and works with the following data types:

  • CHAR

  • VARCHAR2

  • NCHAR

  • NVARCHAR

  • CLOB

  • NCLOB

See Chapter 10 for more on this topic.

POSIX Bracket Expressions

The following POSIX bracket expressions (shown in Table A-1) are available with Oracle 10g Release 2.

Table A-1. POSIX Bracket Expressions

Bracket Expression

Description

Example

[[:alnum:]]

Alphanumeric characters

SELECT REGEXP_SUBSTR(
'3967 S. Bills Dr., Stevens Point, WI
54481', '[[:alnum:]]{4}')
FROM dual;

[[:alpha:]]

Alphabetic characters

SELECT REGEXP_SUBSTR( ...

Get Expert Oracle PL/SQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial