REGEXP_REPLACE让你搜索的字符串的正则表达式模式REPLACE函数的功能。默认情况下，
该函数返回source_char与replace_string取代了正则表达式模式的每个实例。
返回的字符串是在相同的字符集source_char。
语法
Oracle数据库中的REGEXP_REPLACE函数的语法是：
REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )参数
source_char
搜索值的字符表达式。这通常是一个字符列，可以是任何数据类型CHAR，VARCHAR2，NCHAR，NVARCHAR2，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. |
replace_string
可选。匹配的模式将被替换replace_string字符串。如果省略replace_string参数，
将删除所有匹配的模式，并返回结果字符串。
position
可选。在字符串中的开始位置搜索。如果省略，则默认为1。
occurrence
可选。是一个非负整数默认为1，指示替换操作的发生：
如果指定0，那么所有出现将被替换字符串。
如果指定了正整数n，那么将替换第n次出现。
match_parameter
可选。它允许你修改REGEXP_REPLACE功能匹配的行为。它可以是以下的组合：
| 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. |
EXAMPLE – 匹配第一个字
让我们开始使用REGEXP_REPLACE函数替换字符串中的第一个字。
For example:
SELECT REGEXP_REPLACE ('itmyhome is my network id', '^(/S*)', 'luck')
FROM dual;
Result: luck is my network id这个例子会返回”luck is my network id”，
因为它会再字符串的开始找到第一个匹配的字符，然后替换为”luck”
EXAMPLE – 匹配数字字符
我们将使用REGEXP_REPLACE函数来匹配单个数字字符模式。
For example:
SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '/d', '#')
FROM dual;
Result: '#, #, and ## are numbers in this example'此示例将所指定的/d数字将以#字符替换
我们可以改变我们的正则模式来搜索仅两位数字。
For example:
SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(/d)(/d)', '#')
FROM dual;
Result: '2, 5, and # are numbers in this example'这个例子将替换具有两个数字并排指定的(/d)(/d)模式。在这种情况下，它将跳过2和5个数字值和用#字符替换10。
作者：itmyhome
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/4099.html