|
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;
下面这个例子返回指定第三次出现的字符
SELECT REGEXP_SUBSTR (‘i like beijing tiananmen‘,‘(S*)(s)‘,1,3) FROM dual;
作者：itmyhome
再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://www.cnblogs.com/captainbed
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
