Oracle Database/Regular Expression Support

Use regular expressions to search for, match, and replace strings

Regular Expression

Class	Expression	Description
Anchoring Character	^	Start of a line
	$	End of a line
Quantifier Character	*	Match 0 or more times
	+	Match 1 or more times
	?	Match 0 or 1 time
	{m}	Match exactly m times
	{m,}	Match at least m times
	{m, n}	Match at least m times but no more than n times
	\n	Cause the previous expression to be repeated n times
Alternative and Grouping	|	Separates alternates, often used with grouping operator ()
	( )	Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
	[char]	Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters
Posix Character	[:alnum:]	Alphanumeric characters
	[:alpha:]	Alphabetic characters
	[:blank:]	Blank Space Characters
	[:cntrl:]	Control characters (nonprinting)
	[:digit:]	Numeric digits
	[:graph:]	Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
	[:lower:]	Lowercase alphabetic characters
	[:print:]	Printable characters
	[:punct:]	Punctuation characters
	[:space:]	Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
	[:upper:]	Uppercase alphabetic characters
	[:xdigit:]	Hexidecimal characters
Equivalence class	= =	An equivalence classes embedded in brackets that matches a base letter and all of its accented versions. eg, equivalence class '[=a=]' matches ä and â.
Match Option	c	Case sensitive matching
	i	Case insensitive matching
	m	Treat source string as multi-line activating Anchor chars
	n	Allow the period (.) to match any newline character
x	ignore white space characters

REGEXP_LIKE

REGEXP_LIKE performs complex regular expression pattern matching and supports much greater range of string patterns than LIKE. this function is introduced in 10g.

last name begin with T and the 2nd character is either 'o' or 'u'

SELECT last_name
FROM   hr.employees
WHERE  REGEXP_LIKE(last_name, '^T[ou]');

last name begin with 'T' and end with 'r'

select last_name
from   hr.employees
where  REGEXP_LIKE( last_name, '^T.*r$' );

first name is either 'Steven' or 'Stephen'

SELECT first_name
FROM hr.employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

last name contain double vowel characters (ie. 'aa', 'ee', 'ii', 'oo', 'uu') and the matching is non-case sensitive

SELECT last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

REGEXP_INSTR

REGEXP_INSTR performs complex regular expression pattern matching and supports much greater range of string patterns than INSTR. this function is introduced in 10g.

show the position of the 1st lowercase vowel characters

SELECT last_name, REGEXP_INSTR(last_name, '[aeiou]')
FROM   hr.employees;

REGEXP_SUBSTR

REGEXP_SUBSTR performs complex regular expression pattern matching and supports much greater range of string patterns than SUBSTR. this function is introduced in 10g.

extract the 1st character if the last name start with 'A' or 'C'

SELECT last_name, REGEXP_SUBSTR(last_name, '^[AC]')
FROM   hr.employees;

Start at 3rd position, extract 2 characters from the last name

SELECT last_name, REGEXP_SUBSTR(last_name, '..',3)
FROM   hr.employees;

REGEXP_COUNT

REGEXP_COUNT performs count against a value and it is different from the aggregate COUNT function. This function is introduced in 11g.

find the occurrences of the vowel pattern in the last name

SELECT last_name, REGEXP_COUNT( last_name, '[aeiou]' )
FROM   hr.employees;