Description: REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This 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 source_string.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 14th, 2009
[, position [, occurrence
'source_string' is a character expression that serves as the
'pattern' is the regular expression. It is usually a text literal.
'position' is a positive integer indicating the character of
'source_string' where Oracle should begin the search. The
default position is 1.
'occurrence' is a positive integer indicating which occurrence
of pattern in source_string Oracle should search for.
The default is 1.
'match_parameter' is a text literal that lets you change
the default matching behavior of the function.
'match_parameter' can be a combination of
i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of
a line in a multiline string
*/-- Searches for a comma followed by one or more occurrences of -- non-comma characters followed by a comma SELECT
REGEXP_SUBSTR('I had a dog, he had white fur, his name was Bob.',[^,]+,')
RESULT FROM dual;
-- search for 'http://' followed by a string of one or more
-- alphanumeric characters
SELECT REGEXP_SUBSTR('Find Oracle code AT http://www.psoug.org/',
-- given the string 'green|red|blue|yellow|black'
-- in a table column, explode the string along the
-- pipes (vertical bar) and extract the colors:
SELECT REGEXP_SUBSTR(regex_column,'[^|]+', 1, 3)
-- match 'call', 'caller', 'called', or 'calling' in the string
REGEXP_SUBSTR('You have called me too often, she said.','call((ing)|(er)|(ed))')
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.