Oracle的REGEXP_SUBSTR函数简单用法详解数据库

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

正则表达式

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 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.
n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering /n.
Matches the beginning of a string or matches at the end of a string before a newline character.
position

可选。搜索在字符串中的开始位置。如果省略，默认为1，这是第一个位置的字符串。

occurrence

可选。它是模式字符串中的第n个匹配位置。如果省略，默认为1。

match_parameter

可选。它允许你修改regexp_substr功能匹配的行为。它可以是以下的组合：

Allows the period character (.) to match the newline character. By default, the period is a wildcard.
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.
Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

找出匹配的数字

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

4104.html

oracle