Regular functions in SQL

Regular functions in SQL

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.
(? <Name>)	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] + (? <LastName> [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 (? <! 1) \ d {2} ([A-Z]) the input string to match is as follows: 123A456F789C111A 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. It will implement a match-word trusting. "." Matches any character, and of course matches "ing ". Therefore, the Regex engine traces back one bit and stops at 2nd "t", and then matches the specified mode "ing ". However, If you disable the tracing operation: (?>. *) Ing It will implement 0 matching. "." Can match all characters, including "ing"-it cannot match, and thus the matching fails.

 

5. Decision characters

Note: The characters listed above force the processor to execute an if-else decision

6. Replace characters

Note: The above are common replacement characters, not all

VII. Escape Sequence

 

8. Option flag

 

This article permanently updates the link address: