2020年5月10日 星期日

[ORACLE] REGEXP_REPLACE函數應用

資料庫系統均提供以新字串取代目標字串中所有子(舊)字串之功能,ORACLE提供REGEXP_REPLACE函數應用正規表示式於搜尋模式上,以指定字串取代每一個出現的符合模式(Pattern)之子字串。

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子字串將被直接剔除(減碼),如中間具有其他額外字元則為置換功能。

沒有留言:

張貼留言