Oracle Regular expression Matching

Oracle Regular Expressions

In practical applications, you want to exclude field values with Chinese:

Select H.froomnumber from T_broker_house h where Regexp_like (Froomnumber, ' ^ ([a-z0-9a-z]|-) *$ ')

The string ' ^198[0-9]$ ' can match ' 1980-1989 ', and if you want to count the employees of the company who are in the 80 ~89 year, you can use the following SQL statement:

SELECT * FROMEmpe whereRegexp_like(To_char(E.HireDate, 'yyyy'), ' ^198[0-9]$ ');

The metadata commonly used in regular expressions (metacharacter) is as follows:

^ matches the start position of the string.

$ matches the end position of the payment pass.

* Matches one character in front of the character 0 times, 1 times or more. For example 52*oracle can match 5oracle,52oracle,522oracle,5222oracle and so on.

+ matches one character preceding the character 1 or more times. For example, 52+oracle can match 52oracle,522oracle,5222oracle, etc.

? Matches one character before the character 0 or 1 times or more occurrences. For example, 52?oracle can only match 5oracle,52oracle, etc.

{n} matches a string n times and N is a positive integer. For example: The Hel{2}o match is the Hello

{N,m} matches a string at least n times, up to M times. where n and m are integers.

. Matches any single string except NULL

(pattern) This is a sub-expression used to match a specified pattern

X|y matches x or y, where x and Y are one or more characters

[ABC] matches any one of the characters in the parentheses. For example: [AB]BC can match ABC and BBC

[A-z] matches any string within the specified range. For example [A-g]hi can match ahi to Ghi

[::] Specifies a character class that can match any character in the class here the character classes include:

[: Alphanum:] can match characters 0-9, A-Z, A-Z

[: Alpha:] can match characters A-Z, A-Z

[: Blank:] can match a space or TAB key

[:d Igit:] can match the number 0-9

[: Gragh:] can match non-null characters

[:p UNCT:] can be matched. , "' and other punctuation.

[: Upper:] can match characters A-Z

[: Lower:] can match characters A-Z

The regular expressions in Orace can only be used by 4 functions that Oracle specifically designs for regular expressions.

These 4 functions are:

Regexp_like,regexp_instr,regexp_replace,regexp_substr.

an explanation of the Oracle regular expression function

  

A detailed description of the Oracle Regular expression function  oracle10g provides four regular expression functions REGEXP function:regexp_like, Regexp_replace,regexp_instr,regexp_substr.  regexp_like: (Match) compares whether a string matches a regular expression      (srcstr, pattern [, match_option])   regexp_ INSTR: (contains) finds a regular expression in a string, and returns the matching location      (SRCSTR, pattern [, position [, occurrence [, return_option [, Match_ Option]]]        REGEXP_SUBSTR: (extract) returns a substring that matches a regular expression      (srcstr, pattern [, position [, occurrence [, match_option]]]   regexp_replace: (replace) search and replace matching regular expressions      (SRCSTR, pattern [, replacestr [, Position [, occurrence [, match_option]]])       SRCSTR:     &NBSP ;   The character data that is being looked up.       Pattern:       regular expressions.      Occurrence:     number of occurrences. The default is 1.      Position:       start position      Return_option: The default value is 0, which returns the starting position of the mode A value of 1 returns the starting position of the next character that matches the match condition.      REPLACESTR:     A string to replace the matching pattern.      match_option:   match mode option. The default is C.                     c:case sensitive        &NB Sp            i:case insensitive                     n: (.) Match any character (including newline)                     M: string is treated as multiline when there is a newline  ## ############################################################################### #正则表达式由以下标准的元字符 (metacharacters): Characters [[: Alpha:]] any letter. [A-za-z] [[:d Igit:]] any number. [0-9] [[: Alnum:]] Any letters and numbers. [A-za-z0-9] [: Blank:]   [], the standard is space and tab, but there are only spaces in Oracle! [: Cntrl:]   control [: Graph:]   that is, all visible characters, excluding spaces [:p rint:]   printable characters, spaces + all visible characters [[: Space:]] white space characters. [[:p UNCT:]] any punctuation. [[: Upper:]] any uppercase letters. [A-z] [[: Lower:]] any lowercase letter. [A-z] [[: Xdigit:]] Any 16 binary number, equivalent to [0-9a-fa-f]. #######[:class:]   character class. Class such as alnum, digit and so on. [. Element.] Organize classes. Affected by Nls_sort. will be multi-Byte characters are treated as single-byte characters. As in Spanish, CH, ll are all single bytes expressed in multibyte. [=char=]     and other similar. Under the current language (locale), match all of the characters specified by the same type of characters that belong to the same POSIX. As in Spanish, [[[=n=]] Specifies the equivalent class character N, which can match N and in El Ni?o. ########################################################################## #边界符集合 ^   start of each line, single-line mode equivalent to the beginning of the string   At the end of each line, single-line mode is equivalent to the end of the string in single-line mode, ^ and \a equivalents, $ and \z equivalents. ######################################################################### #重复次数集合 *   match 0 or more times--as many times as possible?   0 times or once +   once or more {m} {m}? Exactly m times, greedy and non-greedy like {m,}    {m,}? At least m times {m, n}  {m, n}? min. m up to N times after the above character is added? , such as: *, that is, non-greedy mode. Greedy mode will get as many characters as possible, rather than greedy mode will get as few characters ############################################################## #组合操作符 [...]     One of any characters or character sets within the square brackets. All of the operation symbols are treated as normal symbols, except for the following: ★ Range operator:-but when "-" is at the first or last character of the word list characters, or at the end of the range, it is treated as "-" itself. When the right parenthesis "]" appears in the first position of the list, it is treated as "]" itself. [^ ...]     in square brackets ^ is the first character, which represents a character (...) that does not match any of the subsequent characters.     parentheses, treating complex expressions as a single expression ...     or ABC       and. Write priority comparisons directly with the Word connect prompt: parentheses > Recurrence operators > and > or. For example, (F|HT) TPS?: Represents Ftp:ftps:http:https:##################################################################### #匹配操作符 \ n       forward reference. N is 1~9, which identifies the matching string obtained in parentheses. The direction is from left to right. The escape operator \       treats the action character immediately followed by the normal character. For example, Abc*def can match abdef or abcccdef, but cannot match abc*def, which requires abc\*def to match ################################################## Example parsing of a regular expression matching function: SELECT * FORM tkhxx where  regexp_like (sjhm,   ' ^[1]{1}[35]{1}[[:d igit:]]{9}$ ')   This statement is to check the phone number from the table. ^ indicates that the start $ means end  [] inside the content table for match range {} The number of mobile phone numbers is characterized by a number starting with 1 followed by 3 or 5 plus 9 digits so understanding 1 begins with an expression of ^[1]{1} meaning that the beginning 1 bits contain 13 or 5 expressions for [35]{1}, That is, the second digit that contains 3 or 5 of the 9-digit end is: [[:d igit:]]{9}$ here [:d Igit:] is a special notation, represented as a number, plus a terminator $. That is, the last 9 digits are numbers. #######################################################################  back Reference (backreference): A back reference is a useful feature. It is able to store matching portions of the expression in a temporary buffer for later reuse. Buffers are numbered from left to right and accessed using the \digit symbol. Sub-expressions are displayed with a set of parentheses. The use of a back reference allows for more complex substitution functions.   Below Regexp_replace (' Steven Chen ', ' (. *) ' (. *) ', ' \2, \1 ')   in this function, first in the regular expression function (' Steven Chen ', ' (. *) (. *) ', Two of these parentheses will match Chen, Steven; then use the "\ Number" symbol to refer to it.

123456789

SQL> select regexp_replace(‘Steven Chen‘, ‘(.*) (.*)‘, ‘\2, \1‘) as reversed_name from dual;            REVERSED_NAME      --------------------      Chen, Steven  在DDL中也可以正则表达式，比如Constraint, index, viewSql代码      SQL> alter table person add constraint constraint_zip check (regexp_like(zip, ‘^[[:digit:]]+$‘));            SQL> create index person_idx on person(regexp_substr(last_name, ‘^[[:upper:]]‘));

Http://blog.sina.com.cn/s/blog_69e7b8d701012tuj.html

Oracle Regular expression Matching