Oracle的REGEXP SUBSTR函数简单用法

                       

REGEXP_SUBSTR延伸SUBSTR函数的功能,让你搜索一个正则表达式模式字符串。

这也类似于REGEXP_INSTR,而是返回子字符串的位置,它返回的子字符串本身。

语法

Oracle数据库中的REGEXP_SUBSTR函数的语法是:

REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ]]])
  
  
  • 1

参数

source_char

搜索字符串。可以是任意的数据类型char,VARCHAR2,nchar,CLOB,NCLOB类型

pattern

正则表达式

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
ValueDescription
^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.
\nn 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匹配任何非空白字符.
\AMatches the beginning of a string or matches at the end of a string before a newline character.
\ZMatches 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功能匹配的行为。它可以是以下的组合:

                                                                                                                       
ValueDescription
‘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
  
  
  • 1

下面这个例子返回指定第三次出现的字符

SELECT REGEXP_SUBSTR ('i like beijing tiananmen', '(\S*)(\s)', 1, 3)FROM dual;    --beijing
  
  
  • 1
  • 2


作者:itmyhome

           

再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值