Oracle PL/SQL Tutorial/Regular Expressions Functions/Introduction

Regular Expression Functions

The regular expression functions available in Oracle Database 10g.

REGEXP_LIKE(x, pattern [, match_option])

Returns true when the source x matches the regular expression pattern.

You can change the default matching using match_option, which may be set to:

1. "c", which specifies case sensitive matching (default).
2. "i", which specifies case insensitive matching.
3. "n", which allows you to use the match-any-character operator.
4. "m", which treats x as multiple line.

REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]])

Searches for pattern in x and returns the position at which pattern occurs.

You can supply an optional:

1. start position to begin the search.
2. occurrence that indicates which occurrence of pattern_exp should be returned.
3. return_option that indicates what integer to return. 0 specifies the integer to return is the position of the first character in x; non-zero specifies the integer to return is the position of the character in x after the occurrence.
4. match_option to change the default matching.

REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence [, match_option]]]])

Searches x for pattern and replaces it with replace_string.

The other options have the same meaning as those shown earlier.

REGEXP_SUBSTR(x, pattern [, start [, occurrence [, match_option]]])

Returns a substring of x that matches pattern, which begins at the position specified by start.

The other options have the same meaning as those shown earlier.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

18. 1. Introduction 18. 1. 1. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/RegularExpressionMetacharacters.htm">Regular Expression Metacharacters</a> 18. 1. 2. Regular Expression Functions

Regular Expression Metacharacters

The following table lists some of the metacharacters you can use in a regular expression, along with their meaning and a simple example of their use.

Metacharacters Meaning Examples \ Indicates that the match character is a special character, a literal, or a backreference. (A backreference repeats the previous match.) \n matches the newline character,\\ matches \, \( matches (, \) matches ) ^ Matches the position at the start of the string. ^A matches A if A is the first character in the string. $ Matches the position at the end of the string. $B matches B if B is the last character in the string.

Matches the preceding character zero or more times. ba*rk matches brk, bark, baark, and so on. + Matches the preceding character one or more times. ba+rk matches bark, baark, and so on, but not brk. ? Matches the preceding character zero or one time. ba?rk matches brk and bark only. {n} Matches a character exactly n times, where n is an integer. hob{2}it matches hobbit. {n,m} Matches a character at least n times and at most m times, where n and m are both integers. hob{2,3}it matches hobbit and hobbbit only. . Matches any single character except null. hob.it matches hobait, hobbit, and so on. (pattern) A subexpression that matches the specified pattern. You use subexpressions to build up complex regular expressions. You can access the individual matches, known as captures, from this type of subexpression. anatom(y|ies) matches anatomy and anatomies. x|y Matches x or y, where x and y are one or more characters. war|peace matches war or peace. [abc] Matches any of the enclosed characters. [ab]bc matches abc and bbc. [a-z] Matches any character in the specified range. [a-c]bc matches abc, bbc, and cbc. [: :] Specifies a character class and matches any character in that class. [:alphanum:] matches alphanumeric characters 0-9, A-Z, and a-z.[:alpha:] matches alphabetic characters A-Z and a-z.[:blank:] matches space or tab.[:digit:] matches digits 0-9.[:graph:] matches non-blank characters.[:lower:] matches lowercase alphabetic characters a-z.[:print:] is similar to [:graph:] except [:print:] includes the space character.[:punct:] matches punctuation characters .,"", and so on.[:space:] matches all whitespace characters.[:upper:] matches all uppercase alphabetic characters A-Z.[:xdigit:] matches characters permissible in a hexadecimal number 0-9, A-F, and a-f. [..] Matches one collation element, like a multicharacter element. No example. [==] Specifies equivalence classes. No example. \n This is a backreference to an earlier capture, where n is a positive integer. (.)\1 matches two consecutive identical characters. The (.) captures any single character except null, and the \1 repeats the capture, matching the same character again, therefore matching two consecutive identical characters.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813