Oracle的REGEXP_SUBSTR函数简单用法
来源:互联网 发布:知柏地黄丸副作用 编辑:程序博客网 时间:2024/06/05 19:56
REGEXP_SUBSTR延伸SUBSTR函数的功能,让你搜索一个正则表达式模式字符串。
这也类似于REGEXP_INSTR,而是返回子字符串的位置,它返回的子字符串本身。
语法
Oracle数据库中的REGEXP_SUBSTR函数的语法是:
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ]]])
参数
source_char
搜索字符串。可以是任意的数据类型char,VARCHAR2,nchar,CLOB,NCLOB类型
pattern
正则表达式
Value Description ^ Matches the beginning of a string. If used with a match_parameter of ‘m’, it matches the start of a line anywhere within expression. $ Matches the end of a string. If used with a match_parameter of ‘m’, it matches the end of a line anywhere within expression. * 匹配零个或多个. + 匹配一个或多个出现. ? 匹配零次或一次出现. . 匹配任何字符,除了空. | Used like an "OR" to specify more than one alternative. [ ] Used to specify a matching list where you are trying to match any one of the characters in the list. [^ ] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list. ( ) Used to group expressions as a subexpression. {m} Matches m times. {m,} Matches at least m times. {m,n} Matches at least m times, but no more than n times. \n n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n. [..] Matches one collation element that can be more than one character. [::] Matches character classes. [==] Matches equivalence classes. \d 匹配一个数字字符. \D 匹配一个非数字字符. \w 匹配包括下划线的任何单词字符. \W 匹配任何非单词字符. \s 匹配任何空白字符,包括空格、制表符、换页符等等. \S 匹配任何非空白字符. \A Matches the beginning of a string or matches at the end of a string before a newline character. \Z Matches at the end of a string. *? Matches the preceding pattern zero or more occurrences. +? Matches the preceding pattern one or more occurrences. ?? Matches the preceding pattern zero or one occurrence. {n}? Matches the preceding pattern n times. {n,}? Matches the preceding pattern at least n times. {n,m}? Matches the preceding pattern at least n times, but not more than m times.position
可选。搜索在字符串中的开始位置。如果省略,默认为1,这是第一个位置的字符串。
occurrence
可选。它是模式字符串中的第n个匹配位置。如果省略,默认为1。
match_parameter
可选。它允许你修改regexp_substr功能匹配的行为。它可以是以下的组合:
Value Description ‘c’ 区分大小写的匹配. ‘i’ 不区分大小写的匹配. ‘n’ Allows the period character (.) to match the newline character. By default, the period is a wildcard. ‘m’ expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line. ‘x’ Whitespace characters are ignored. By default, whitespace characters are matched like any other character.Examples
找出匹配的数字
SELECT REGEXP_SUBSTR ('hello my phone is 520 ', '[0-9]+') FROM dual; --520
下面这个例子返回指定第三次出现的字符
SELECT REGEXP_SUBSTR ('i like beijing tiananmen', '(\S*)(\s)', 1, 3)FROM dual; --beijing
作者:itmyhome
4 0
- Oracle的REGEXP_SUBSTR函数简单用法
- Oracle的SUBSTR()和INSTR()以及REGEXP_SUBSTR()函数的用法
- Oracle正规则函数的用法,regexp_substr,REGEXP_LIKE,REGEXP_INSTR,REGEXP_REPLACE
- oracle REGEXP_SUBSTR 函数的使用
- Oracle中REGEXP_SUBSTR函数的使用
- Oracle中REGEXP_SUBSTR函数的使用说明
- REGEXP_SUBSTR函数用法
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数
- Oracle函数值REGEXP_SUBSTR
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数 .
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数
- Oracle中REGEXP_SUBSTR函数
- Oracle regexp_substr函数
- OSI七层网络模型百科:
- 在pdf中绘制表格与插入图片
- Android Studio中代码如何调试?
- Jetty服务器jmx监控
- 初步了解委托
- Oracle的REGEXP_SUBSTR函数简单用法
- 求rar文件jsp+servlet+MySQL实现的微博项目源码成功运行的源代码,有一个pudn会员账号作为回报
- online_judge_1138
- 2015-12-13复习之CSS3 border 详解border-image
- Java 类加载 流程
- ruby--数值类(Numeric)
- ITMS-90096错误解决
- fgets、gets和scanf的区别
- 获取手机短信