Regular functions in SQL

Oracle supports the following four regular expression functions: 1, REGEXP_LIKE: similar to LIKE, which is much more powerful than LIKE. 2, REGEXP_INSTR: With INSTR

Oracle supports the following four regular expression functions: 1, REGEXP_LIKE: similar to LIKE, which is much more powerful than LIKE. 2, REGEXP_INSTR: With INSTR

There are four functions that support regular expressions in Oracle:
1. REGEXP_LIKE: similar to LIKE, it is much more powerful than LIKE.
2. REGEXP_INSTR: similar to INSTR.
3. REGEXP_SUBSTR: similar to SUBSTR.
4. REGEXP_REPLACE: similar to REPLACE.

REGEXP_REPLACE (source_string, pattern, replace_string, position, occurtence, match_parameter) function (new 10g function)
Description: String replacement function. It is equivalent to an enhanced replace function. Source_string specifies the source character expression; pattern specifies the rule expression; replace_string specifies the string to be replaced; position specifies the start search position; occurtence specifies the nth string to be replaced; match_parameter specifies the text string for the default matching operation. ITPUB personal space. x mz \ n' r­ g9 [1'
The replace_string, position, occurtence, and match_parameter parameters are optional.

REGEXP_SUBSTR (source_string, pattern [, position [, occurrence [, match_parameter]) function (new 10g function)
Description: return the substring of the matching mode. It is equivalent to the enhanced substr function. Source_string specifies the source character expression, pattern specifies the rule expression, position specifies the start search position, occurtence specifies the nth string to replace, and match_parameter specifies the text string of the default matching operation.
The position, occurtence, and match_parameter parameters are optional.

The value of match_option is as follows:
'C' indicates that the matching time zone is case sensitive (default );
'I' indicates that the matching is case insensitive;
'N' allows operators that can match any character;
'M' uses x as a string that contains multiple rows.

REGEXP_LIKE (source_string, pattern [, match_parameter]) function (new 10g function)
Description: return a string that meets the matching mode. It is equivalent to an enhanced like function. Source_string specifies the source character expression, pattern specifies the rule expression, and match_parameter specifies the text string of the default matching operation.
The position, occurtence, and match_parameter parameters are optional.

REGEXP_INSTR (source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]) function (10 Gb new function)
Description: This function searches for pattern and returns the first position of the pattern. You can specify the start_position you want to start searching. The default occurrence parameter is 1, unless you specify the mode you want to find. The default value of return_option is 0. It returns the starting position of the mode. If the value is 1, it returns the starting position of the next character that meets the matching conditions.

1. Matching characters

2. Repeated characters

3. Positioning characters

Note: positioning characters can be applied to characters or combinations and placed on the left or right side of the string

4. Group characters

Group characters

Definition

For example

()

This character can be used to combine the characters matching the internal mode of the brackets. It is a capture group, that is, the pattern matching character is used as the final setting of the ExplicitCapture option-by default, the characters are not part of the matching.

Input string: ABC1DEF2XY

Regular Expression that matches 3 characters from A to Z and 1 digit :( [A-Z] {3} \ d)

Match 1 = ABC1; Match 2 = DEF2

Each match corresponds to one group: the first group of Match2 = ABC; the first group of Match2 = DEF

With Reverse reference, you can access the Group through its number in the regular expression, C #, class Group, and GroupCollection. If the ExplicitCapture option is set, the content captured by the group cannot be used.

(? :)

This character can be combined with characters matching the internal mode of the brackets. It is a non-capturing group, which means that the characters in the mode will not be captured as a group, but it forms part of the final matching result. It is basically the same as the group type above, but the option "ExplicitCapture" is set.

Input string: 1A BB SA1 C

Match A number or A letter from A to Z, followed by A regular expression of any word character :(? : \ D | [A-Z] \ w)

It will produce 3 matches: 1 match = 1A; 2 matches = BB; 3 matches = SA

But no group is captured.

(? )

This option combines the characters matching the inner mode of the brackets and names them with the values specified in the angle brackets. In a regular expression, you can use a name for reverse reference instead of a number. Even if you do not set the ExplicitCapture option, it is also a capture group. This means that reverse references can be accessed by matching characters in the Group or by using the Group class.

The input string is Characters in Sienfeld encoded Jerry Seinfeld, Eline Benes, Cosno Kramer and George Costanza, which can match their names and capture the Regular Expression of surnames in a group of llastnames as follows: \ B [A-Z] [a-z] + (? [A-Z] [a-z] +) \ B

It produces four matches: First Match = Jerry Seinfeld; Second Match = Eline Benes; Third Match = Cosmo Kramer; Fourth Match = George Costanza

Each match corresponds to a lastName group:

1st match: lastName group = Seinfeld

2nd match: lastName group = Benes

3rd match: lastName group = Kramer

4th match: lastName group = Costanza

Whether or not the option ExplictCapture is set, the group will be captured.

(? =)

Declaration. The right side of the Declaration must be the pattern specified in brackets. This mode does not constitute part of the final match

Regular Expression \ S + (? =. NET) The input string to be matched is: The ages were Java, C #. NET, VB. NET, C, Jscript. NET, Pascal

The following match will be generated :〕

C #

VB

JScript.

(?!)

Negative declaration. It specifies that the pattern cannot be placed on the right of the statement. This mode does not constitute part of the final match

\ D {3 }(?! [A-Z]) the input string to be matched is 123A 456 789111C

The following match will be generated:

456

789

(? <=)

Reverse positive declaration. The left side of the statement must be the specified mode in parentheses. This mode does not constitute part of the final match

Regular Expression (? <= New) ([A-Z] [a-z] +) The input string to match is: The following states, New Mexico, West Virginia, Washington, New England

It will generate the following matching:

Mexico

England

(?

Reverse positive declaration. The left side of the statement must not be the specified mode in parentheses. This mode does not constitute part of the final match

Regular Expression (?

It will implement the following matching:

56F

89C

(?>)

Non-backtracking group. Prevents Regex engine backtracking and prevents one matching.

Assume that all words ending with "ing" must be matched. The input string is as follows: He was very trusing

The regular expression is. * ing.