The regular expression cannot leave the four functions of Oracle:
1. Regexp_like
2. Regexp_substr
3. Regexp_instr
4. Regexp_replace
What's the use of the function name can probably guess,
Regexp_like can only be used for conditional expressions, and the like are similar, but using the regular expression matching and syntax is simple:
Regexp_substr function, and substr similar to pick up in accordance with regular expressions to describe character substring, syntax is as follows:
Regexp_instr function, similar to instr, conform to the regular expressions used for calibration of the starting position of character substring syntax is as follows:
Regexp_replace function, and the replace similar to replace conform to the regular expression string syntax is as follows:
Here to parse the meaning of several parameters:
1, source_char, the input string, can be a column or string constants, variables,
2, the pattern, regular expressions,
3, match_parameter, matching options,
Scope: I: case-insensitive. C: case sensitive; N: the dot does not match the newline symbol; M: multiline mode; X: extension mode, ignore white space characters in regular expressions,
4, the position, identity from which characters start regular expression match,
5, occurrence, identify which one match group,
6, replace_string, replace string,
Sql code
Oracle 10 g regular expression usage REGEXP_LIKE
/*
In the ORACLE support regular expression function mainly has the following four:
1, REGEXP_LIKE: similar to the function of the LIKE
2, REGEXP_INSTR: similar to the function of the INSTR
3, REGEXP_SUBSTR: similar to the function of the SUBSTR
4, REGEXP_REPLACE: similar to the function of the REPLACE
They on the usage and Oracle SQL functions LIKE, INSTR, SUBSTR and REPLACE usage is the same,
But they use POSIX regular expression instead of the old percent (%) and wildcard character (_),
POSIX regular expression by standard metacharacters (metacharacters) :
'^' matches the starting position of the input string, in square brackets expressions used, meanwhile it said not to accept the character set,
'$' at the end of the input string matching position, set up a RegExp Multiline attribute of an object, if is $horse also
With '\ n' or '\ r',
'. 'matches any single character except newline,
'? Match the sub-expression in front of zero or one,
'+' match the sub-expression in front of one or more,
'*' match the sub-expression in front of zero or more,
'|' indicates a choice between two examples' ^ ([a-z] + | [0-9] +) $' variegated said all lowercase letters or Numbers,
String,
'()' mark the beginning and the end of a child expression,
'[]' tag in a bracket expressions,
'{m, n} a precise scope occurrences, m=& lt; Occurrences & lt;=n, said '{m}' m times, said '{m,}' at least
M times,
\ num matching num, which is a positive integer num, reference to the matching is acquired,
Character clusters:
[[: alpha:]] any letters,
[[: digit:]] any Numbers,
[[: alnum:]] any letters and Numbers,
[[: space:]] any white characters,
[[: upper:]] any capital letters,
[[: lower:]] any lowercase,
[[: punct:]] any punctuation,
[[: xdigit:]] any hexadecimal number, the equivalent of [0-9 a - fA - F],
All kinds of operators operation priority
\ escape characters
(), (? :), (?=), parentheses and brackets []
*, +,? , {n} {n,}, {n, m} qualifier
^ and $, anymetacharacter position and order
|
*/
example:
Create a table and initializes the data
- create a table
The create table FZQ (
Id varchar (4),
The value varchar (10)
);
- insert the data
Insert into FZQ values (' 1 ', '1234560');
Insert into FZQ values (' 2 ', '1234560');
Insert into FZQ values (' 3 ', '1 b3b560');
Insert into FZQ values (' 4 ', 'ABC');
Insert into FZQ values (' 5 ', 'abcde');
Insert into FZQ values (' 6 ', 'ADREasx');
Insert into FZQ values (' 7 ', '123 45');
Insert into FZQ values (' 8 ', 'adc DE');
Insert into FZQ values (' 9 ', '. The adc, DE ');
Insert into FZQ values (' 10 ', '1 b).
Insert into FZQ values (' 10 ', 'abcbvbnb');
Insert into FZQ values (' 11 ', '11114560');
Insert into FZQ values (' 11 ', '11124560');
- submit
commit;
Validation is about regexp_like and the like:
TBS @ TICKET> Select * from FZQ where value like '1 ____60';
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
1 1234560
2 1234560
3 1 b3b560
TBS @ TICKET> Select * from FZQ where regexp_like (value, '1... 60 ');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
1 1234560
2 1234560
3 1 b3b560
11 11114560
11 11124560
TBS @ TICKET> Select * from FZQ where regexp_like (the value of '1 [0-9] {4} 60');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
1 1234560
2 1234560
11 11114560
11 11124560
TBS @ TICKET> Select * from FZQ where regexp_like (the value of '1 [[: digit:]] {4} 60');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
1 1234560
2 1234560
11 11114560
11 11124560
TBS @ TICKET> Select * from FZQ where not regexp_like (value, '^ [[: digit:]] + $');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
3 1 b3b560
4 the ABC
5 abcde
6 ADREasx
7 45 123
8 the adc DE
Adc, 9. DE
10 1 b
10 abcbvbnb
Selected line 9,
TBS @ TICKET> Is not a pure digital record, query the value
TBS @ TICKET> Select * from FZQ where not regexp_like (value, '^ [[: digit:]] + $');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
3 1 b3b560
4 the ABC
5 abcde
6 ADREasx
7 45 123
8 the adc DE
Adc, 9. DE
10 1 b
10 abcbvbnb
Selected line 9,
TBS @ TICKET> - the query value does not contain any number of records,
TBS @ TICKET> Select * from FZQ where regexp_like (value, '^ [^ [: digit:]] + $');
ID VALUE
-- -- -- -- -- -- -- -- -- -- --
4 the ABC
5 abcde
6 ADREasx
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull