Quick Search:
Oracle PL/SQL Code Library
Find Or Post Oracle Jobs
Oracle Discussion & Chat
 Oracle PL/SQL: Regular Expressions - REGEXP_SUBSTR Jump to:  
Category: >> Oracle PL/SQL >> Regular Expressions - REGEXP_SUBSTR Bookmark and Share

<< lastnext >>

Snippet Name: Regular Expressions - REGEXP_SUBSTR

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.

Also see:
» Regular Expressions - REGEXP_LIKE
» Regular Expressions - REGEXP_REPLACE
» Regular Expressions - RegExp examples ...
» Regular Expressions - RegExp examples ...
» Regular Expressions - RegExp examples...
» Regular Expressions - Regexp Cheat She...
» RegExp - Append first name first lette...

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 14th, 2009

REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])
'source_string' is a character expression that serves as the 
search value.
'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       
REGEXP_SUBSTR('I had a dog, he had white fur, his name was Bob.',[^,]+,') 
-- search for 'http://' followed by a string of one or more 
-- alphanumeric characters
SELECT REGEXP_SUBSTR('Find Oracle code AT http://www.psoug.org/',
'http://([[:alnum:]]+){3,4}/?') RESULT
FROM dual;
-- 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)
FROM regexp;
-- match 'call', 'caller', 'called', or 'calling' in the string
REGEXP_SUBSTR('You have called me too often, she said.','call((ing)|(er)|(ed))')
FROM dual;

Oracle Magazine
and Oracle White Papers

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.

Click here to find out more
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 30 users online    © 2009 psoug.org

Forgot your password?