Oracle正则表达式

Oracle正则表达式 正则表达式具有强大、便捷、高效的文本处理功能。能够添加、删除、分析、叠加、插入和修整各种类型的文本和数据。Oracle从10g开始支持正则表达式。 下面通过一些例子来说明使用正则表达式来处理一些工作中常见的问题。 1. REGEXP_SUBSTR REGEXP_SUBSTR 函数使用正则表达式来指定返回串的起点和终点，返回与source_string 字符集中的VARCHAR2 或CLOB 数据相同的字符串。 语法： --1.REGEXP_SUBSTR与SUBSTR函数相同，返回截取的子字符串 REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]]) 注： srcstr 源字符串 pattern 正则表达式样式 position 开始匹配字符位置 occurrence 匹配出现次数 match_option 匹配选项（区分大小写）   1.1 从字符串中截取子字符串 SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+') FROM dual; Output: 1PSN [[:alnum:]]+ 表示匹配1个或者多个字母或数字字符。 SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+', 1, 2) FROM dual; Output: 231 与上面一个例子相比，多了两个参数 1 表示从源字符串的第一个字符开始查找匹配 2 表示第2次匹配到的字符串（默认值是“1”，如上例） select regexp_substr('@@/231_3253/ABc','@*[[:alnum:]]+') from dual; Output: 231 @* 表示匹配0个或者多个@ [[:alnum:]]+ 表示匹配1个或者多个字母或数字字符 注意：需要区别“+”和“*”的区别 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]*') from dual; Output: @ @+ 表示匹配1个或者多个@ [[:alnum:]]* 表示匹配0个或者多个字母或数字字符 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]+') from dual; Output: Null @+ 表示匹配1个或者多个@ [[:alnum:]]+ 表示匹配1个或者多个字母或数字字符 select regexp_substr('@1PSN/231_3253/ABc125','[[:digit:]]+$') from dual; Output: 125 [[:digit:]]+$ 表示匹配1个或者多个数字结尾的字符 select regexp_substr('@1PSN/231_3253/ABc','[^[:digit:]]+$') from dual; Output: /ABc [^[:digit:]]+$ 表示匹配1个或者多个不是数字结尾的字符 select regexp_substr('Tom_Kyte@oracle.com','[^@]+') from dual; Output: Tom_Kyte [^@]+ 表示匹配1个或者多个不是“@”的字符 select regexp_substr('1PSN/231_3253/ABc','[[:alnum:]]*',1,2) from dual; Output: Null [[:alnum:]]* 表示匹配0个或者多个字母或者数字字符 注：因为是匹配0个或者多个，所以这里第2次匹配的是“/”（匹配了0次），而不是“231”，所以结果是“Null” 1.2 匹配重复出现 查找连续2个小写字母 SELECT regexp_substr('Republicc Of Africaa', '([a-z])/1', 1, 1, 'i') FROM dual; Output: cc ([a-z]) 表示小写字母a-z /1 表示匹配前面的字符的连续次数 1 表示从源字符串的第一个字符开始匹配 1 第一次出现符合匹配结果的字符 i 表示区分大小写 查找连续3个6,7,8,9中的数字 SELECT CASE WHEN regexp_like('Patch 10888 applied', '([6-9])/1/1') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 1.3 其他一些匹配样式 查找网页地址信息 SELECT regexp_substr('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+/.?){3,4}/?') RESULT FROM dual Output: http://www.oracle.com 其中： http:// 表示匹配字符串“http://” ([[:alnum:]]+/.?) 表示匹配1次或者多次字母或数字字符，紧跟0次或1次逗号符 {3,4} 表示匹配前面的字符最少3次，最多4次 /? 表示匹配一个反斜杠字符0次或者1次 提取csv字符串中的第三个值 SELECT regexp_substr('1101,Yokohama,Japan,1.5.105', '[^,]+', 1, 3) AS output FROM dual; Output: Japan 其中： [^,]+ 表示匹配1个或者多个不是逗号的字符 1 表示从源字符串的第一个字符开始查找匹配 3 表示第3次匹配到的字符串 注：这个通常用来实现字符串的列传行 --字符串的列传行 SELECT regexp_substr('1101,Yokohama,Japan,1.5.105', '[^,]+', 1, LEVEL) AS output FROM dual CONNECT BY LEVEL <= length('1101,Yokohama,Japan,1.5.105') - length(REPLACE('1101,Yokohama,Japan,1.5.105', ',')) + 1; Output: 1101 Yokohama Japan 1.5.105 这里通过LEVEL来循环截取匹配到的字符串。 下面这个例子，查找源字符串中是否包含 kid 、kids或者kidding 这三个字符串 SELECT CASE WHEN regexp_like('Why does a kid enjoy kidding with kids only?', 'kid(s|ding)*', 'i') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 其中： kid 表示字符串kid (s|ding)* 表示匹配0次或者多次字符“s”或者“ding” i 表示不区分大小写 2. REGEXP_INSTR REGEXP_INSTR 函数使用正则表达式返回搜索模式的起点和终点。REGEXP_INSTR 的语法如下所示。REGEXP_INSTR 返回一个整数，指出搜索模式的开始或结束的位置，如果没有发现匹配的值，则返回0。 语法： --2.REGEXP_INSTR与INSTR函数相同，返回字符串位置 REGEXP_INSTR(srcstr, pattern [, position [, occurrence [, return_option [,match_option]]]]) 与REGEXP_SUBSTR一样，它也有变量pattern、position(开始位置)、occurrence 和match_parameter；这里主要介绍一下新参数return_option 的作用，它允许用户告诉Oracle，模式出现的时候，要返回什么内容。 具体如下面的例子所示： --如果return_option 为0 则，Oracle 返回第一个字符出现的位置。这是默认值，与INSTR的作用相同 SELECT regexp_instr('abc1def', '[[:digit:]]') output FROM dual; Output: 4 --如果return_option 为1，则Oracle 返回跟在所搜索字符出现以后下一个字符的位置。 --例如，下面的查询返回了在串中发现的第一个数字的位置： SELECT regexp_instr('abc1def', '[[:digit:]]',1,1,1) output FROM dual; Output: 5 3. REGEXP_LIKE 除了上面列出的正则表达式函数，还可以使用REGEXP_LIKE 函数。REGEXP_LIKE 支持在where 子句中使用正则表达式。 语法： --3.REGEXP_LIKE与LIKE函数相同，返回布尔类型 REGEXP_LIKE(srcstr, pattern [,match_option]) 3.1 验证字符串全部由字母组成 SELECT CASE WHEN regexp_like('Google', '^[[:alpha:]]{6}$') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 在上面这个例子中，通过正则表达式来验证字符串是否由6个字母组成。解释一下表达式 ^[[:alpha:]]{6}$。 ^ 表示行的开始 [[:alpha:]] 表示字母 {6} 表示匹配的次数，这里表示匹配6次 $ 表示行的结尾 3.2 验证字符串全部由小写字母组成 SELECT CASE WHEN regexp_like('Terminator', '^([[:lower:]]{3,12})$') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 这里如果我们把输入字符串改成“terminator”，则结果就是匹配了。上面的例子中，通过正则表达式来验证字符串是否由3到12个小写字母组成。其中： {3,12} 表示匹配次数，最少3次，最多12次 3.3 区分大小写 SELECT CASE WHEN regexp_like('Republic Of India', 'of', 'c') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: No Match Found 上面的例子中，通过正则表达式来验证字符串是否包含小写的“of”。其中： c 表示完全匹配，区分大小写 如果改成下面这样，则不区分大小写： SELECT CASE WHEN regexp_like('Republic Of India', 'of', 'i') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 其中： i 表示不区分大小写 3.4 匹配第n位置的字符 SELECT CASE WHEN regexp_like('ter*minator', '^...[^[:alnum:]]') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 在上面的例子中，通过正则表达式来验证字符串“ter*minatory”的第4位的字符。解释一下表达式^...[^[:alnum:]]，其中： ^ 表示行的开始 . 表示匹配任意一个的字符(… 表示3个任意的字符) [^[:alnum:]] 表示匹配不是字母和数字(^ 在方括号内，表示否定) 3.5 查找控制字符 SELECT CASE WHEN regexp_like('Super' || chr(13) || 'Star', '[[:cntrl:]]') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 上面的例子中，通过正则表达式来验证字符串中是否包含控制符。（13在ASCII码中表示回车符） 3.6 验证SSN SELECT CASE WHEN regexp_like('987-65-4321', '^[0-9]{3}-[0-9]{2}-[0-9]{4}$') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found Input: 987-654-3210 Output: No match found 其中： ^ 表示行的开始 [0-9]{3} 表示3个数字 - 表示连字号“-” [0-9]{2} 表示2个数字 - 表示连字号“-” [0-9]{4} 表示2个数字 $ 表示行的结束 3.7 验证email地址 SELECT CASE WHEN regexp_like('tom_kyte@oracle.com', '^([[:alnum:]]+(_?|/.))[[:alnum:]]*@[[:alnum:]]+(/.([[:alnum:]]+)){1,2}$') THEN 'Match Found' ELSE 'No Match Found' END AS output FROM dual; Output: Match Found 将上面的正则表达式通过字符“@”分为两部分： 前半部分 ^([[:alnum:]]+(_?|/.))[[:alnum:]]* ，其中： ^ 表示行的开始 ([[:alnum:]]+(_?|/.)) 表示匹配以1个或多个字符或者数字字符，紧跟着字符“或者字符“.” [[:alnum:]]* 表示匹配0个或者多个字母或者数字字符 后半部分 [[:alnum:]]+(/.([[:alnum:]]+)){1,2}$，其中： [[:alnum:]]+ 表示匹配1个或者多个字母或者数字字符 (/.([[:alnum:]]+)){1,2} 表示匹配1个或者2个，“.”加上1个或者多个字母或者数字字符（例如：.com.cn 或者 .co.in） $ 表示行的结尾 输入：tom.kyte@oracle.com 输出：Match Found 输入：tom-kyte@oracle.com 输出：No Match Found 注:上面出现的“/.”中“/”表示转义符，因为“”是关键字符，匹配任意字符，所以需要转义符。 4. REGEXP_REPLACE REGEXP_REPLACE 函数是用另外一个值来替代串中的某个值。例如，可以用一个匹配数字来替代字母的每一次出现。REGEXP_REPLACE的格式如下所示 语法： --4.REGEXP_REPLACE与REPLACE函数相同，替换原字符串中的字符内容 REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [,match_option]]]]) 4.1 查找替换空格符 SELECT regexp_replace('Help Earth Stay Green', '[[:blank:]]{2,8}', ' ') FROM dual; Output: Help Earth Stay Green 上面的例子中，用正则表达式将字符串中多个空格替换为一个空格。其中： [[:blank:]]{2,8} 表示2到8个空格 注：这里有目的的没有包含(^$) 元字符，是因为需要匹配字符串中的任何位置，所以不需要这两个位置匹配符。^[[:blank:]]{2,8}$ 这样表示字符串中只包含2-8个空格 4.2 格式化字符串 SELECT regexp_replace('04099661234', '([[:digit:]]{3})([[:digit:]]{4})([[:digit:]]{4})', '(/1) /2-/3') AS formatted_phone FROM dual; Output: (040) 9966-1234 ([[:digit:]]{3}) 表示3个数字 ---