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。
