10g REGULAR EXPRESSIONS (REGEXP_SUBSTR)

[amitpatel66]

REGEXP_SUBSTR

The REGEXP_SUBSTR function, much like the SUBSTR function, extracts part of a string

Syntax:

REGEXP_SUBSTR(source_string, pattern[, position [, occurrence[, match_parameter]]])

Example:

In the following SQL query, the string that matches the pattern , [^,]*, is returned. The regular expression searches for a comma followed by a space; then zero or more characters that are not commas, as indicated by [^,]*; and lastly looks for another comma. The pattern will look somewhat similar to a comma-separated values string.

SELECT REGEXP_SUBSTR('first field, second field , third field',
       ', [^,]*,') reg_substr
  FROM dual
 
REG_SUBSTR
------------------
, second field   ,