oralce正则表达式函数

正则表达式 前端之家
前端之家收集整理的这篇文章主要介绍了oralce正则表达式函数，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
官方网址：点击打开链接@H_301_2@
Oracle Regular Expressions@H_301_2@
Version 11.1@H_301_2@
General Information@H_301_2@

Anchoring Characters@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
^@H_301_2@ Anchor the expression to the start of a line@H_301_2@
$@H_301_2@ Anchor the expression to the end of a line@H_301_2@

Equivalence Classes@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
= =@H_301_2@ Oracle supports the equivalence classes through the POSIX '[==]' Syntax. A base letter and all of its accented versions constitute an equivalence class. For example,the equivalence class '[=a=]' matches ä and â. The equivalence classes are valid only inside the bracketed expression@H_301_2@

Match Options@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
c@H_301_2@ Case sensitive matching@H_301_2@
i@H_301_2@ Case insensitive matching@H_301_2@
m@H_301_2@ Treat source string as multi-line activating Anchor chars@H_301_2@
n@H_301_2@ Allow the period (.) to match any newline character@H_301_2@

Posix Characters@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
[:alnum:]@H_301_2@ Alphanumeric characters@H_301_2@
[:alpha:]@H_301_2@ Alphabetic characters@H_301_2@
[:blank:]@H_301_2@ Blank Space Characters@H_301_2@
[:cntrl:]@H_301_2@ Control characters (nonprinting)@H_301_2@
[:digit:]@H_301_2@ Numeric digits@H_301_2@
[:graph:]@H_301_2@ Any [:punct:],[:upper:],[:lower:],and [:digit:] chars@H_301_2@
[:lower:]@H_301_2@ Lowercase alphabetic characters@H_301_2@
[:print:]@H_301_2@ Printable characters@H_301_2@
[:punct:]@H_301_2@ Punctuation characters@H_301_2@
[:space:]@H_301_2@ Space characters (nonprinting),such as carriage return,newline,vertical tab,and form Feed@H_301_2@
[:upper:]@H_301_2@ Uppercase alphabetic characters@H_301_2@
[:xdigit:]@H_301_2@ Hexidecimal characters@H_301_2@

Quantifier Characters@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
*@H_301_2@ Match 0 or more times@H_301_2@
?@H_301_2@ Match 0 or 1 time@H_301_2@
+@H_301_2@ Match 1 or more times@H_301_2@
{m}@H_301_2@ Match exactly m times@H_301_2@
{m,}@H_301_2@ Match at least m times@H_301_2@
{m,n}@H_301_2@ Match at least m times but no more than n times@H_301_2@
\n@H_301_2@ Cause the prevIoUs expression to be repeated n times@H_301_2@

Alternative Matching And Grouping Characters@H_301_2@
Character Class@H_301_2@ Description@H_301_2@
|@H_301_2@ Separates alternates,often used with grouping operator ()@H_301_2@
( )@H_301_2@ Groups subexpression into a unit for alternations,for quantifiers,or for backreferencing (see "Backreferences" section)@H_301_2@
[char]@H_301_2@ Indicates a character list; most Metacharacters inside a character list are understood as literals,with the exception of character classes,and the ^ and - Metacharacters@H_301_2@

Demo Table@H_301_2@		 CREATETABLEtest (
testcolVARCHAR2(50));

INSERTINTOtestVALUES('abcde');
INSERTINTOtestVALUES('12345');
INSERTINTOtestVALUES('1a4A5');
INSERTINTOtestVALUES('12a45');
INSERTINTOtestVALUES('12aBC');
INSERTINTOtestVALUES('12abc');
INSERTINTOtestVALUES('12ab5');
INSERTINTOtestVALUES('12aa5');
INSERTINTOtestVALUES('12AB5');
INSERTINTOtestVALUES('ABCDE');
INSERTINTOtestVALUES('123-5');
INSERTINTOtestVALUES('12.45');
INSERTINTOtestVALUES('1a4b5');
INSERTINTOtestVALUES('1 3 5');
INSERTINTOtestVALUES('1 45');
INSERTINTOtestVALUES('1 5');
INSERTINTOtestVALUES('a b c d');
INSERTINTOtestVALUES('a b c d e');
INSERTINTOtestVALUES('a e');
INSERTINTOtestVALUES('Steven');
INSERTINTOtestVALUES('Stephen');
INSERTINTOtestVALUES('111.222.3333');
INSERTINTOtestVALUES('222.333.4444');
INSERTINTOtestVALUES('333.444.5555');
INSERTINTOtestVALUES('abcdefabcdefabcxyz');
COMMIT;@H_301_2@
REGEXP_COUNT
Syntax@H_301_2@ REGEXP_COUNT(<source_string>,<pattern>[[,<start_position>],[<match_parameter>]])

-- match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters@H_301_2@

Count's occurrences based on a regular expression@H_301_2@		 SELECTREGEXP_COUNT@H_301_2@(testcol,'2a',1,'i') RESULT
FROMtest;

SELECTREGEXP_COUNT@H_301_2@(testcol,'e','i') RESULT
FROMtest;@H_301_2@
REGEXP_INSTR
Syntax@H_301_2@ REGEXP_INSTR(<source_string>,<start_position>][,<occurrence>][,<return_option>][,<match_parameter>][,<sub_expression>]])@H_301_2@

Find character 'o' followed by any 3 alphabetic characters: case insensitive@H_301_2@

Our thanks to Cassio for spotting a typo here.@H_301_2@

 SELECTREGEXP_INSTR@H_301_2@('500O@H_301_2@racle Pkwy,Redwood Shores,CA','[o][[:alpha:]]{3}','i') RESULT
FROMDUAL;

SELECTREGEXP_INSTR@H_301_2@('500 Oracle@H_301_2@Pkwy,'i') RESULT
FROMDUAL;

SELECTREGEXP_INSTR@H_301_2@('500 Oracle Pkwy,Redwood Sho@H_301_2@res,2,Redwood Shores,@H_301_2@CA','i') RESULT
FROMDUAL;@H_301_2@
Find the position of try,trying,tried or tries@H_301_2@ SELECTREGEXP_INSTR@H_301_2@('We are trying to make the subject easier.','tr(y(ing)?|(ied)|(ies))') RESULTNUM
FROMDUAL;@H_301_2@
Using Sub-Expression option@H_301_2@ SELECTtestcol,REGEXP_INSTR@H_301_2@(testcol,'ab','i',0@H_301_2@)
FROMtest;

SELECTtestcol,1@H_301_2@)
FROMtest;

SELECTtestcol,'a(@H_301_2@b)@H_301_2@',1@H_301_2@)
FROMtest;@H_301_2@
REGEXP_LIKE
Syntax@H_301_2@ REGEXP_LIKE(<source_string>,<pattern>,<match_parameter>)@H_301_2@

AlphaNumeric Characters@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alnum:@H_301_2@]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alnum:@H_301_2@]]{3}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alnum:@H_301_2@]]{5}');@H_301_2@

Alphabetic Characters@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alpha:@H_301_2@]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alpha:@H_301_2@]]{3}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:alpha:@H_301_2@]]{5}');@H_301_2@

Control Characters@H_301_2@		 INSERTINTOtestVALUES('zyx' ||CHR(13) || 'wvu');
COMMIT;

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:cntrl:@H_301_2@]]{1}');@H_301_2@

Digits@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:digit:@H_301_2@]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:digit:@H_301_2@]]{3}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:digit:@H_301_2@]]{5}');@H_301_2@

Lower Case@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:lower:@H_301_2@]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:lower:@H_301_2@]]{2}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:lower:@H_301_2@]]{3}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:lower:@H_301_2@]]{5}');@H_301_2@

Printable Characters@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:print:@H_301_2@]]{5}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:print:@H_301_2@]]{6}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:print:@H_301_2@]]{7}');@H_301_2@

Punctuation@H_301_2@		 TRUNCATETABLEtest;

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:punct:@H_301_2@]]');@H_301_2@

Spaces@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:space:@H_301_2@]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:space:@H_301_2@]]{2}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:space:@H_301_2@]]{3}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:space:@H_301_2@]]{5}');@H_301_2@

Upper Case@H_301_2@		 SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:upper:]]');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:upper:]]{2}');

SELECT*
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'[[:upper:]]{3}');@H_301_2@
Values Starting with 'a%b'@H_301_2@@H_301_2@ SELECTtestcol
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'^ab*');@H_301_2@
'a' is the third value@H_301_2@ SELECTtestcol
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'^..a.');@H_301_2@
Contains two consecutive occurances of the letter 'a' or 'z'@H_301_2@ SELECTtestcol
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'([az])\1','i');@H_301_2@
Begins with 'Ste' ends with 'en' and contains either 'v' or 'ph' in the center@H_301_2@ SELECTtestcol
FROMtest
WHEREREGEXP_LIKE@H_301_2@(testcol,'^Ste(v|ph)en$');@H_301_2@
Use a regular expression in a check constraint@H_301_2@ CREATETABLEmytest (c1VARCHAR2(20),
CHECK (REGEXP_LIKE@H_301_2@(c1,'^[[:alpha:]]+$')));@H_301_2@

Identify SSN

Thanks: Byron Bush HIoUG@H_301_2@		 CREATETABLEssn_test (
ssn_colVARCHAR2(20));

INSERTINTOssn_testVALUES('111-22-3333');
INSERTINTOssn_testVALUES('111=22-3333');
INSERTINTOssn_testVALUES('111-A2-3333');
INSERTINTOssn_testVALUES('111-22-33339');
INSERTINTOssn_testVALUES('111-2-23333');
INSERTINTOssn_testVALUES('987-65-4321');
COMMIT;

SELECTssn_col
from ssn_test
WHEREREGEXP_LIKE@H_301_2@(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');@H_301_2@
REGEXP_REPLACE
Syntax@H_301_2@ REGEXP_REPLACE(<source_string>,
<replace_string>,<position>,<occurrence>,<match_parameter>)@H_301_2@
Looks for the pattern xxx.xxx.xxxx and reformats pattern to (xxx) xxx-xxxx@H_301_2@ col testcol format a15
col result format a15

SELECTtestcol,REGEXP_REPLACE@H_301_2@(testcol,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') RESULT
FROMtest
WHERELENGTH(testcol) = 12;@H_301_2@
Put a space after every character@H_301_2@ SELECTtestcol,'(.)','\1 ') RESULT
FROMtest
WHEREtestcol like 'S%';@H_301_2@
Replace multiple spaces with a single space@H_301_2@ SELECTREGEXP_REPLACE@H_301_2@('500 Oracle Parkway,'( ){2,}',' ') RESULT
FROMDUAL;@H_301_2@
Insert a space between a lower case character followed by an upper case character@H_301_2@ SELECTREGEXP_REPLACE@H_301_2@('George McGovern','([[:lower:]])([[:upper:]])','\1 \2') CITY
FROMDUAL;@H_301_2@
(Produces 'George Mc Govern')
Replace the period with a string (note use of '\')@H_301_2@ SELECTREGEXP_REPLACE@H_301_2@('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE
FROMDUAL;@H_301_2@
Demo@H_301_2@ CREATETABLEt(
testcolVARCHAR2(10));

INSERTINTOtVALUES('1');
INSERTINTOtVALUES('2 ');
INSERTINTOtVALUES('3 new ');

col newval format a10

SELECTLENGTH(testcol) len,testcol origval,
REGEXP_REPLACE@H_301_2@(testcol,'\W+$',' ') newval,
LENGTH(REGEXP_REPLACE@H_301_2@(testcol,' ')) newlen
FROMt;@H_301_2@
Code snippet courtesy of Valentin Matak.
This is a handy way to remove duplicate characters from a string. This example shows the 1+ repeatability qualifier in use.@H_301_2@		 SELECTREGEXP_REPLACE@H_301_2@('ABBBCABBCCCAABAAAAA','(A|B|C)\1+','\1')
FROMDUAL;@H_301_2@
Code snippet courtesy of Jonathan Linder.
This checks for a valid email address and then extracts the domain name.@H_301_2@		 SELECTREGEXP_REPLACE@H_301_2@('bugs.bunny@gmail.com','^(\S+)@(\S+)','\2')
FROMDUALd
WHERE
REGEXP_LIKE@H_301_2@('bugs.bunny@gmail.com','^[A-Za-z0-9._%+-]+@([A-Za-z0-9-]+.)?+[A-Za-z]{2,63}$')@H_301_2@
Code snippet courtesy of Juraj Drusc.
This example will convert Oracle's SYS_GUID() to a JAVA UUID.@H_301_2@		 SELECTlower(REGEXP_REPLACE@H_301_2@(sys_guid(),
'([[:alnum:]]{8})([[:alnum:]]{4})([[:alnum:]]{4})([[:alnum:]]{4})([[:alnum:]]{12})',
'1-2-3-4-5'))FROMDUAL;@H_301_2@
REGEXP_SUBSTR
Syntax@H_301_2@ REGEXP_SUBSTR(source_string,pattern
[,position [,occurrence
[,match_parameter]]])@H_301_2@
Searches for a comma followed by one or more occurrences of non-comma characters followed by a comma@H_301_2@ SELECTREGEXP_SUBSTR@H_301_2@('500 Oracle Parkway,',[^,]+,') RESULT
FROMDUAL;@H_301_2@
Look for http:// followed by a substring of one or more alphanumeric characters and optionally,a period (.)@H_301_2@ col result format a50

SELECTREGEXP_SUBSTR@H_301_2@('Go to http://www.oracle.com/products and click on database',
'http://([[:alnum:]]+\.?){3,4}/?') RESULT
FROMDUAL;@H_301_2@
Extracts try,tried or tries@H_301_2@ SELECTREGEXP_SUBSTR@H_301_2@('We are trying to make the subject easier.','tr(y(ing)?|(ied)|(ies))')
FROMDUAL;@H_301_2@
Extract the 3rd field treating ':' as a delimiter@H_301_2@ SELECTREGEXP_SUBSTR@H_301_2@('system/pwd@orabase:1521:sidval',
'[^:]+',3) RESULT
FROMDUAL;@H_301_2@

Extract from string with vertical bar delimiter@H_301_2@		 CREATETABLEregexp (
testcolVARCHAR2(50));

INSERTINTOregexp
(testcol)
VALUES
('One|Two|Three|Four|Five');

SELECT*FROMregexp;

SELECTREGEXP_SUBSTR@H_301_2@(testcol,'[^|]+',3@H_301_2@)
FROMregexp;@H_301_2@
Equivalence classes@H_301_2@ SELECTREGEXP_SUBSTR@H_301_2@('iSelfSchooling NOT ISelfSchooling','[[=i=]]SelfSchooling') RESULT
FROMDUAL;@H_301_2@

Parsing Demo@H_301_2@		 set serveroutput on

DECLARE
xVARCHAR2(2);
yVARCHAR2(2);
cVARCHAR2(40) := '1:3,4:6,8:10,3:4,7:6,11:12';
BEGIN
x := REGEXP_SUBSTR(c,'[^:]+',1);
y := REGEXP_SUBSTR(c,'[^,]+',3,1);

dbms_output.put_line(x ||' '|| y);
END;
/@H_301_2@
Gary Whitaker wrote in with an addition to this parsing demo,and had the following comments:

The parsing demo above uses the regular expression '[^,]+' whichdoes not workwhen there is a@L_416_301@element in the list. This could result in returning the wrong element's data. Consider this simple example with comments:

set serveroutput on

DECLARE
xVARCHAR2(1);
yVARCHAR2(1);
zVARCHAR2(1);
cVARCHAR2(40) := '1,4,5,6,7';
BEGIN

-- Works as expected if the value you seek is before any null value in the list:

x := REGEXP_SUBSTR(c,2);

-- This form only returns the 4th element when all elements are present.
-- It will return the 4thnon-nullelement,which in this example is really '5',
-- which could be misleading.
-- if you are really after the 4th element regardless if there is a null element:

y := REGEXP_SUBSTR(c,4);

-- This form gets the actual 4th element,allowing for the null element. -- Get the 1st substring of the 4th instance of a set of characters that are not a comma,-- when followed by a comma or the end of the line: z := REGEXP_SUBSTR(c,'([^,]*)(,|$)',NULL,1); dbms_output.put_line(x); dbms_output.put_line(y); dbms_output.put_line(z); END; /

猜你在找的正则表达式相关文章

常用正则表达式-手机号、身份证、邮箱
一、校验数字的表达式 1 数字：^[0-9]*$ 2 n位的数字：^d{n}$ 3 至少n位的数字：^d{n,}$ 4...
JS正则表达式详解
正则表达式非常有用，查找、匹配、处理字符串、替换和转换字符串，输入输出等。下面整理一...
组内正则培训记录
0. 注： 不同语言中的正则表达式实现都会有一些不同。下文中的代码示例除特别说明的外，都...
高级正则表达式技术（Python版）
 正则表达式是从信息中搜索特定的模式的一把瑞士军刀。它们是一个巨大的工具库，其中的...
史上最全最常用的正则表达式
一、校验数字的表达式 数字：^[0-9]*$ n位的数字：^\d{n}$ 至少n位的数字：^\d{n,...
正则表达式工具
正则表达式初步
分享5个可视化的正则表达式编辑工具
　　正则表达式使用单个字符串来描述、匹配一系列符合某个句法规则的字符串。在很多文本编...
分享5个可视化的正则表达式编辑工具
正则表达式使用单个字符串来描述、匹配一系列符合某个句法规则的字符串。在很多文本编辑器...
[知识积累]--正则表达式记忆表
在工作中常常遇到正则表达式问题，有时候又会忘记这则的语法。 下面就分享一份正则表达式记...
算法设计模式多媒体技术正则表达式ElasticsearchFlinkHadoopIDE
受约束摘*day25Java常用类库置信lamda留存持续录入年后正则表达式303.17regularexpre匹多模适20130322基础理论pathmunge涵义reectok需要转义的特资源分享validationex简明魔法里弄形如源码实现完备actionscript