Regular functions in SQL

There are four main functions in Oracle that support regular expressions:
1,regexp_like: Similar to the function of like, it is much more powerful.
2,REGEXP_INSTR: Similar to the function of INSTR.
3,REGEXP_SUBSTR: Similar to the function of SUBSTR.
4,regexp_replace: Similar to the function of REPLACE.

regexp_replace (source_string,pattern,replace_string,position,occurtence,match_parameter) function (10g new function )
Description: A string substitution function. Equivalent to an enhanced replace function. source_string Specifies the source character expression, pattern specifies the rule expression, replace_string specifies the string to replace, position specifies the starting search location, and occurtence specifies the nth string to be substituted; Match_ parameter specifies the text string for the default match operation. Itpub personal space. x mz\ n ' r-g9[1 '
Where the Replace_string,position,occurtence,match_parameter parameter is optional.

regexp_substr (source_string, pattern[,position [, occurrence[, Match_parameter]]) function (10g new function )
Description: Returns a substring of the matching pattern. Equivalent to an enhanced substr function. source_string Specifies the source character expression, pattern specifies the rule expression, position specifies the starting search location, occurtence specifies the nth string to replace occurrences, and Match_parameter specifies the text string for the default matching operation.
Where the Position,occurtence,match_parameter parameter is optional.

The values of the match_option are as follows:
The ' C ' description is case-sensitive at the time of the match (default value);
' I ' indicates that the match is not case-sensitive;
' n ' allows the use of operators that can match any character;
' m ' takes x as a string that contains multiple lines.

regexp_like (source_string, pattern[, Match_parameter]) function (10g new function )
Description: Returns a string that satisfies the matching pattern. Equivalent to an enhanced like function. source_string Specifies the source character expression; pattern specifies the rule expression; match_parameter Specifies the text string for the default matching action.
Where the Position,occurtence,match_parameter parameter is optional.

regexp_instr (source_string, pattern[, start_position[, occurrence[, return_option[, Match_parameter]]) function (10g new function )
Description: The function looks for pattern and returns the first position of the pattern. Feel free to specify the start_position you want to start searching for. The occurrence parameter defaults to 1 unless you specify that you want to find a pattern that appears next. The default value of Return_option is 0, which returns the starting position of the pattern, and a value of 1 returns the starting position of the next character that matches the matching criteria.

a . Match character

two . Repeating characters

three . Positioning characters

Note: Positional characters can be applied to characters or combinations, placed on the left or right side of a string

Four . grouping characters

Grouping characters	fixed righteousness	Lift Example
（）	This character can be combined with a character that matches the pattern in parentheses, which is a capturing group, which means that the pattern-matching character is finally set to the Explicitcapture option-the default state character is not part of the match	The input string is: Abc1def2xy Match 3 characters from A to Z and a regular expression of 1 numbers: ([a-z]{3}\d) will produce two matches: match 1=ABC1; Match 2=def2 Each match corresponds to a group: the first group of Match1 =ABC; The 1th group of MATCH2 =def With a reverse reference, you can access the group by its number in the regular expression and by the C # and class Group,groupcollection. If you set the Explicitcapture option, you cannot use the content captured by the group
（?:）	This character can combine characters that match the pattern in parentheses, which is a non-capturing group, which means that the character of the pattern is not captured as a group, but it forms part of the final match result. It is basically the same as the group type above, but sets the option Explicitcapture	The input string is: 1A BB SA1 C Matches a number or a letter A to Z, followed by any word character's regular expression: (?: \ d| [A-z]\w] It will produce 3 matches: every 1 matches =1a; every 2 matches =bb; every 3 matches =sa But no group was captured.
(?<name>)	This option combines the characters that are matched by the pattern in parentheses and names the group with the values specified in the angle brackets. In a regular expression, you can use the name for a reverse reference without having to use a number. Even if the explicitcapture option is not set, it is also a capturing group. This means that a reverse reference can take advantage of a matching character within a group, or access the	input string by using the group class: characters in Sienfeld included Jerry seinfeld,elaine Benes,cosno Kramer and George Costanza are able to match their names, and the regular expression for capturing surnames in a group Llastname is: \b[a-z][a-z]+ (?< lastname>[a-z][a-z]+) \b It produced 4 matches: first Match=jerry Seinfeld; Second Match=elaine Benes; Third Match=cosmo Kramer; Fourth Match=george Costanza Each match corresponds to a lastName group: 1th match: LastName Group=seinfeld 2nd time match: LastName Group=benes 3rd match: LastName group=kramer 4th time matching: lastName Group=costanza Regardless of whether option explictcapture is set, Groups are captured
(? =)	is declaring. The right side of the declaration must be the pattern specified in parentheses. This pattern does not form part of the final match	The input string to match for the regular expression \s+ (? =.net) is: The languages were java,c#.net,vb.net,c,jscript.net,pascal will produce the following match:) C# Vb Jscript.
（?! ）	Negative declarations. It specifies that the pattern cannot be close to the right side of the statement. This pattern does not form part of the final match	\D{3} (?! [A-z]) The input string to match is: 123A 456 789111C The following matches will be produced: 456 789
(? <=)	Reverse positive declaration. The left side of the declaration must be a specified pattern within parentheses. This pattern does not form part of the final match	The input string to match for the regular expression (? <=new) ([a-z][a-z]+) is: The following states,new mexico,west Virginia,washington, New England It will produce the following matches: Mexico England
(? <!)	Reverse positive declaration. The left side of the declaration must not be a specified pattern within parentheses. This pattern does not form part of the final match	The input string to match for the regular expression (? <!1) \d{2} ([A-z]) is as follows: 123a456f789c111a It will implement the following match: 56F 89C
(?>)	Non-backtracking group. Prevents the Regex engine from backtracking and prevents one match from being implemented	Suppose you want to match all the words ending with "ing". The input string is as follows: He was very trusing The regular expression is:. *ing It will implement a match-the word trusting. “.” Matches any character, and certainly matches "ing". So, the Regex engine goes back one and stops at 2nd "T" and then matches the specified pattern "ing". However, if backtracking operations are disabled: (? >.*) ing It will achieve 0 matches. “.” Can match all characters, including "ing"--cannot match, thus matching failed

Five . Decision characters

Note: The characters listed in the table above force the processor to perform a if-else decision

Six . Replace character

Note: The above is a common replacement character, not all

Seven . Escape sequences

Eight . Option Flag

Reference: http://www.cnblogs.com/gkl0818/archive/2009/02/12/1389521.html

Regular functions in SQL