Quick Search:
 Oracle PL/SQL: Regular Expressions: REGEXP_SUBSTR Jump to:  
Category: >> Oracle PL/SQL >> Regular Expressions: REGEXP_SUBSTR  

<< 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 t...
» Regular Expressions: RegExp examples t...
» Regular Expressions: RegExp examples o...
» Regular Expressions: Regexp Cheat Sheet
» RegExp: Append first name first letter...

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;

   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org