SQL Regular Expression

Ext.: http://blog.csdn.net/weiwenhp/article/details/6943834#t1

Directory:

1. Meta-characters in regular expressions
2. Regexp_like
3. Regexp_instr
4. Regexp_replace
5. Regexp_substr

When we want to do some simple confused query with percent sign (%), the wildcard character (_) is available. Where% expresses a string of any length, _ represents any one character.

For example, the SELECT * from emp where ename like ' s% ' or ename as ' s_ ';

However, if you use these two symbolic SQL statements in a complex query, it can be very complex and not necessarily achievable. Starting with Oracle 10g, a regular expression used in other programming languages has been introduced.

There are mainly regexp_like,regexp_replace,regexp_substr,regexp_instr four regular expression functions.

Metacharacters in regular Expressions:

Metacharacters	Mean	Example
\	Indicates that the character to match is a special character, a constant, or a reference to the latter. (after the reference repeats the last match)	\ n matches the line break \ \ match \< Span id= "mathjax-span-5" class= "Mo" > with ( match)
^	Match the starting position of the string	^a matches Arwen. But does not match Barwen.
$	Match the end position of the string	en$ matches Arwen. But does not match Arwenb.
*	Matches the preceding character 0 or more times	A*rwen can match Rwen or Aaarwen.
+	Matches the preceding character 1 or more times	A+rwen can match Arwen or Aarwen. But cannot match Rwen.
?	Match the preceding character 0 or 1 times	A?rwen can match Arwen or Rwen. But cannot match Aarwen.
N	Matches the preceding character exactly n times, where n is an integer	Ar{2}wen can match Arrwen. But cannot match Arwen or Arrrwen.
{N,m}	Matches the preceding character at least n times, up to M times. If written as {N,} indicates a minimum of n times. No upper limit.	Ar{1,2}wen can match Arwen,arrwen. But does not match Awen or Arrrwen.
.	Dot number, match any single character except NULL, newline	ARW.N. can match Arwen,arwin. But cannot match arween or arwn.
(pattern)	Pattern in parentheses is a child regular expression that matches a sub-expression of the specified pattern pattern.	Parentheses, in fact, are like parentheses in a general language expression. Sometimes extra parentheses can enhance readability. For another use, see the description of \ n below.
X|y	Match "or"	X|y can match x or y
[ABC]	can match any single character in ABC	HELLO[ABC] can match Helloa,hellob,helloc
[A-z]	can match any single character within the specified range	Hell[a-z] can match hello or hellz
[::]	Specifies a character class that can match any character in the class	[: Alphanum:] can match the character 0-9, A-Z, A-Z [: Alpha:] can match the character A-Z, A-Z [: blank:] can match the space or tab [:D Igit:] can match the number 0-9 [: graph:] can match the non-null character [: Lower:] can match the lowercase letter A-Z [:p rint:] Similar to [: graph:], except that [:p rint:] including the space character [:p UNCT:] can match the punctuation., "" and so on [: space:] can match all the null characters [: Upper :] can match uppercase letters A-Z [: xdigit:] can match hexadecimal digits 0-9, a-f, a-f
\ n	This is a post-reference to the previous match hit, where n is a positive integer	ARW (en) \1 can match Arwenen. Note that \1 must be preceded by a parenthesized subexpression.

1.regexp_like:

Regexp_like (X,pattern[,match_option]) to see if x matches the pattern, the function can also provide an optional parameter Match_option string that describes the default matching options. The value of the match_option is as follows: ' C ' indicates case-sensitive when matching (default); ' I ' description is case-insensitive when matching; ' n ' (.)  A dot can represent all the individual characters, including line breaks (I don't know where to wrap.) only know that the SQL can use Chr (10) to represent the line break. The ' m ' string is treated as multi-line when there is a newline. This will match the end of each line. Otherwise $ only matches the last position of the string.

Example: SELECT * from emp where regexp_like (ename, ' ^a[a-z]*n$ '); You can find lines in ename that begin with a N. For example, ename is Arwen or Arwin or Anden. But Arwen cannot be matched. Because the default is case-sensitive. If the SELECT * from EMP where regexp_like (ename, ' ^a[a-z]*n$ ', ' I ') you can find the row records ename to Arwen.

2.REGEXP_INSTR:

Regexp_instr (x,pattern[,start[,occurrence[,return_option[, Match_option]]) is used to find pattern in X. Returns the position where pattern appears in X. The match position starts at 1. You can refer to the String function INSTR (), Parameter correlation: ' Start ' position to start finding, ' occurrence ' description should return the position of the first occurrence of pattern, ' return_option ' indicates what integer should be returned. If the parameter is 0, the integer to be returned is the position of one character in X, and if the argument is a non-0 integer, the integer to be returned is the position of the character that appears after pattern in X; ' match_option ' modifies the default matching settings. The phase in the Regexp_like With.

Example:

DECLARE

V_result INTEGER ;

BEGIN

SELECT regexp_instr (' Hello World ',' o ', 1,1,0) into v_result

From DUAL;

Dbms_output. Put_Line (V_result);

END;

The result is 5. The position of the first occurrence of the letter O

If Regexp_instr (' Hello World ', ' o ', 1,1,n) where n is an integer other than 0. For example, 1,3. The result is 6. Represents the position of the first character after the letter o appears.

If Regexp_instr (' Hello World ', ' o ', 1,2,0) the result is 9. Indicates the position of the second occurrence of the letter O.

3.regexp_replace:

Regexp_replace (x,pattern[,replace_string[,start[,occurrence[, Match_option]]]) is used to find the pattern in X and replace it with the Replae_ String can refer to String function REPLACE (), parameter with regexp_instr function

Example:

DECLARE

V_result VARCHAR2 (90);

BEGIN

SELECT regexp_replace (' Hello World ',' o ', ' X ', #) into v_result

From DUAL;

Dbms_output. Put_Line (V_result);

END;

The result is Hellx world.

If Regexp_replace (' Hello World ',' o ', ' x '), then the result is Hellx Wxrld.

If Regexp_replace (' Hello World ',' o ', ' x ', ",") then the result is Hello Wxrld.

4.REGEXP_SUBSTR:

Regexp_substr (x,pattern[,start[,occurrence[, Match_option]]) is used to find pattern in X and return. You can refer to the String function SUBSTR () and the parameter with the REGEXP_INSTR function.

For example:

DECLARE

V_result VARCHAR2 (255);

BEGIN

SELECT regexp_substr (' Hello World ', ' l{2} ') into v_result

From DUAL;

Dbms_output. Put_Line (V_result);

END ;

The result is LL

Query to a matching string to return a matching character. Return empty if not found.

SQL Regular Expression