Oracle Database/Regular Expression Support

List the benefits of using regular expressions 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;