REGEXP_REPLACE函數應用上類似TRANSLATE函數及REPLACE函數，TRANSLATE提供多次單一字元置換功能，REPLACE則是將符合子字串將以另一字串取代，REGEXP_REPLACE函數則是提供以正規表示式於字串中搜尋以符合模式之子字串。REGEXP_REPLACE語法如下:
REGEXP_REPLACE(source_string
, pattern
[,
replace_string [,start_position [,nth_occurrence, [match_parameter]]]]
)
|
source_string
搜運算式，支援字元型態如 VARCHAR2, CHAR, NVARCHAR2, NCHAR,
CLOB等型態。
pattern
使用正規表示式找出符合資料，基本功能整理如下表，可藉由組合運算字元達成彈性功能。
字元
|
類別
|
功能
|
範例
|
\
|
逸出字元
|
將下一個字元標記為常值而非運算式字元
|
\.將"."視為小數點或句點
如IP: 192\.168\.0\.1
|
^
|
錨點/反向
|
開始位置；如應於中括([])中使用則表示否定(反向)
|
^be: bear、beer
[^0-9]: a、b (非數字)
|
$
|
錨點
|
結尾位置
|
at$: cat、hat
|
.
|
字元
|
任何字元，除換行符號(\n)外。
|
a.c: a1c、abc、axc、a#c
|
|
|
擇一
|
表示二擇一 (OR)
|
Ja(son|mes): Jason、James
|
*
|
次數
|
任意次數(包含0次)
|
ab*c: ac、abc、abbc
|
+
|
次數
|
任意次數(不含0次)
|
a+: a、aa、aaa 、aaa
|
?
|
次數
|
0次或1次
|
ab?c: ab、abc
|
{n}
|
次數
|
n次
|
x{3}: xxx
|
{n, m}
|
次數
|
n 次至m 次
|
x{2,4}: xx、xxx、xxxx
|
{n,}
|
次數
|
n 次以上
|
x{3,}: xxx、xxxx
|
()
|
分組
|
與小括號內所有字元完全相符(含順序)之字串
|
(th): this、that
Ja(son|mes): Jason、James
|
[]
|
分組
|
與中括號中任一字元符合之字串
|
[abc]: a、b、c
ha[nr]d: hand、hard
|
-
|
分組
|
根據括號內字元範圍比對字串任一部分
|
[0-9]: 數字0~9
[a-z]: 小寫字母a~z
B[1-3]: B1、B2、B3
|
\num
|
引用
|
後向引用符合匹配子字串
|
|
[:alnum:]
|
POSIX
|
任何字母或數字，等同[a-zA-Z0-9]
|
|
[:alpha:]
|
POSIX
|
任何字母，等同[a-zA-Z]
|
|
[:digit:]
|
POSIX
|
任何數字，等同[0-9]
|
|
[:lower:]
|
POSIX
|
任何小寫字母字元，等同[a-z]
|
|
[:punct:]
|
POSIX
|
任何標點符號，等同[][!"#$%&'()*+,./:;<=>?@\^_`{|}~-]
|
|
[:space:]
|
POSIX
|
任何空白字元(無法呈現) ，等同
|
|
[:upper:]
|
POSIX
|
任何大寫字母字元，等同[A-Z]
|
replacement_string
選擇性參數。匹配模式字串將被置換為replace_string字串。如省略replace_string參數，則將刪除所有匹配模式字串後傳回。
start_position
選擇性參數。指定字串中的搜索開始位置。預設為1。
nth_appearance
選擇性參數。不為負值之整數，預設為1，指示置換執行生效之出現次數：
0，每次出現(符合)均被置換。如指定正整數(n)，則於第n次出現時才會被置換。
match_parameter
選擇性參數，常用為為區分大小寫('c')，此為預設值，以及不區分大小寫('i')，其他參數較少使用，請自行參閱官方文件。
Value
|
Description
|
'c'
|
區分大小寫(case-sensitive，預設)
|
'i'
|
不區分大小寫(case-insensitive)
|
以下說明REGEXP_REPLACE函數使用上優點，使用常見REPLACE函式以新字串取代目標字串中所有符合條件之子（舊）字串，範例中欲將［AB...D..E］字串中之空白字元（為清楚顯示改以"."替代）均調整為1個，但多餘空白字元的數量未必相同，如[AB與D]為3個、而[D與E]間為2個空白字元，期待均能以1個空白字元取代。
SQL
|
SELECT VAL
, REGEXP_REPLACE(VAL, '(\.){2,}', '.') "REGEXP_REPLACE"
, REPLACE(VAL, '..', '.') "REPLACE"
FROM
(
SELECT 'AB...D..E' VAL
FROM DUAL
)
註: 由於小數點符號(.)為保留字，因此需使用逸出字元(\)。
|
結果
|
 |
但呼叫REPLACE函數時需精確設定置換標的，如指定2個空白字元時，則[AB與D]間又額外多遺留1個空白字元，需使用巢狀呼叫才可解決；如改用REGEXP_REPLACE函數時，可直接將取代目標設定為2個(含)以上空白字元，即可滿足所需。
以下四個案例介紹基本用法，範例及說明整理如下表
#
|
SQL
|
說明
|
1
|
--每一碼分別取出並加上後置空白
SELECT REGEXP_REPLACE('abc', '(.)', '\1 ') FROM DUAL;
-------------------------
abc
a b c
--移除後小括號則不正確，當使用後向引用需搭配小括號
SELECT REGEXP_REPLACE('abc', '.', '\1 ') FROM DUAL;
-------------------------
\1 \1 \1
|
. è 任何字元
\1 è 代表前面Pattern
|
2
|
--每三碼分別取出並加上"|" (半導體BIN)
SELECT REGEXP_REPLACE('001002012', '(.{3})', '\1|') FROM DUAL
-------------------------
001002012
001|002|012|
|
. è 任何字元
{3} è 出現3次
|
3
|
--字串, 字元兩兩互調
SELECT REGEXP_REPLACE('abc 123', '(.)(.)', '\2\1') FROM DUAL;
-------------------------
abc 123
ba c213
|
後向引用符合匹配子字串順序對調即可達成兩互調。
|
4
|
--第3碼字元置換-方法1
SELECT REGEXP_REPLACE('1234', '.', 'X', 1, 3) FROM DUAL;
--第3碼字元置換-方法2
SELECT
REGEXP_REPLACE('1234', '(^.{2})(.)(.*)$', '\1X\3') FROM DUAL;
-------------------------
1234
12X4
|
先前《[MSSQL] STUFF字串置換函數使用方法及範圍》文章中採討MSSQL的STUFF函數可簡便方式將原有序號進行升碼、減碼或置換等，但ORACLE並無可直接對應之函數，可使用REGEXP_REPLACE函數亦可達成，以下範例說明升碼、減碼及置換(以位置置換)。
操作
|
SQL
|
異動前後
|
升碼
|
--升碼處理
SELECT
REGEXP_REPLACE('202005123','(^.{6})(.*)$','\10\2')
FROM dual;
|
202005123
2020050123
|
減碼
|
--減碼處理
SELECT REGEXP_REPLACE('2020050123','(^.{6})(.{1})(.*)$','\1\3') FROM dual;
|
2020050123
202005123
|
置換
|
SELECT
REGEXP_REPLACE('2020050123','(^.{4})(.{2})(.*)$','\1May\3') FROM dual;
|
2020050123
2020May0123
|
將以減碼為例，指令為REGEXP_REPLACE('2020050123','(^.{6})(.{1})(.*)$','\1\3')以參數分別說明如下。
1. 特徵模式(Pattern): (^.{6})(.{1})(.*)$
Pattern字串為(^.{6})(.{1})(.*)$，依小括號拆分成三個子群組，亦將source_string對應擷取資料整理繪製如下。
\1
|
\2
|
\3
|
|||||||
(^.{6})
|
(.{1})
|
(.*)$
|
|||||||
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
2
|
0
|
2
|
0
|
0
|
5
|
0
|
1
|
2
|
3
|
將Pattern字串中各子組群中所代表意義，逐字說明。
#
|
2020050123
|
說明
|
1
|
(^.{6})
è202005
|
^ è 字首
. è 任何字元
{6} è 出現6次
由字首起算之前6碼è202005
|
2
|
(.{1})
è5
|
. è 任何字元
{1} è 出現1次
由#1後起算第1碼(即第7碼)è5
|
3
|
(.*)$
è 123
|
. è 任何字元
* è 出現任意次數(包含0次)
$ è 字尾
由#2後至字尾之剩餘所有字元è123
|
2. 置換字串(replacement_string): \1\3
置換字串為\1\3，『\num』代表後向引用符合匹配子字串功能，根據前一參數特徵模式(Pattern)定義將source_string所拆分之子字串，本置換字串『\1\3』即代表使用後向引用結果中的1st、3rd部分，由於省略2nd部分，且參數間無其他額外字元，意指2nd子字串將被直接剔除(減碼)，如中間具有其他額外字元則為置換功能。
沒有留言:
張貼留言