Oracle10g Changes to SUBSTR
The SUBSTR function has been extended by the regexp_substr
function provided in Oracle Database 10g.
The regexp_substr function extends the functionality of the
SUBSTR function by letting you search a string for a POSIX or
Unicode compliant regular expression pattern. It is also similar
to regexp_instr, but instead of returning the position of the
substring, it returns the substring itself.
The regexp_substr function is useful if you need the contents
of a match string, but not its position in the source string. The
function returns the string as VARCHAR2 or CLOB data in the same
character set as the supplied source_string.
Here is an example use of the regexp_substr:
SQL> SELECT
2 REGEXP_SUPSTR('5035 Forest Run Trace, Alpharetta, GA',
3 ',[^,]+,') "Test"
4 FROM dual;
Test
------------------
, Alpharetta,
In this example we search for a comma, followed by one or more
characters immediately followed by a comma.
Let's look at the multi-lingual regular expression syntax used
in these new functions. |
