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: bearbeer
[^0-9]: ab (數字)
$
錨點
結尾位置
at$: cathat
.
字元
任何字元換行符(\n)外。
a.c: a1cabcaxca#c
|
擇一
表示二擇一 (OR)
Ja(son|mes): JasonJames
*
次數
任意次數(包含0)
ab*c: acabcabbc  
+
次數
任意次數(不含0)
a+: aaaaaa aaa
?
次數
0次或1
ab?c: ababc
{n}
次數
n
x{3}: xxx
{n, m}
次數
n 次至m
x{2,4}: xxxxxxxxx
{n,}
次數
n 次以上
x{3,}: xxxxxxx
()
分組
與小括號內所有字元完全相符(含順序)字串
(th): thisthat
Ja(son|mes): JasonJames
[]
分組
與中括號中任一字元符合之字串
[abc]: abc
ha[nr]d: handhard
-
分組
根據括號內字元範圍比對字串任一部分
[0-9]: 數字0~9
[a-z]: 小寫字母a~z
B[1-3]: B1B2B3
\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但多餘空白字元的數量未必相同[ABD]3[DE]間為2個空白字元期待均能以1個空白字元取代
SQL
SELECT VAL
     , REGEXP_REPLACE(VAL, '(\.){2,}', '.') "REGEXP_REPLACE"
     , REPLACE(VAL, '..', '.')                 "REPLACE"
FROM
     (
     SELECT 'AB...D..E' VAL    
     FROM DUAL
     )
: 由於小數點符號(.)為保留字因此需使用逸出字元(\)
結果
但呼叫REPLACE函數時需精確設定置換標的如指定2個空白字元時[ABD]間又額外多遺留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字串置換函數使用方法及範圍》文章中採討MSSQLSTUFF函數可簡便方式將原有序號進行升碼減碼或置換等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』即代表使用後向引用結果中的1st3rd部分由於省略2nd部分且參數間無其他額外字元意指2nd子字串將被直接剔除(減碼)如中間具有其他額外字元則為置換功能

沒有留言:

張貼留言