2020年5月5日 星期二

[ORACLE] 正規表示式函數應用(Regular Expression, Regex)-以REGEXP_LIKE為例

Oracle提供支援POSIX 方法以擴展先前所用百比符號(%)和底線符號_模糊比對功能將以REGEXP_LIKE為例介紹正規表示式用法語法如下:
REGEXP_LIKE(expression, pattern [, match_parameter ] )

Expression
運算式支援字元型態如 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]


match_parameter
選擇性參數常用為為區分大小寫('c')此為預設值以及不區分大小寫('i')其他參數較少使用請自行參閱官方文件

Value
Description
'c'
區分大小寫(case-sensitive預設)
'i'
不區分大小寫(case-insensitive)
以下為測試資料將以實例解說用途。
CATE
VAL
1
12399
2
12,599
3
xxz
4
1XZ fff
5
112999

測試範例
#
SQL
結果
1
--找中具有"12"
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '12');
SELECT * FROM T0504 WHERE Val LIKE  '%12%'
12399
12,599
112999
2
--找出字首為"12"
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '^12');
SELECT * FROM T0504 WHERE Val LIKE  '12%'
12399
12,599
3
--找出字首為"1", 字尾為99
/* 建議自行測試以下3組SQL
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '1..99');
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '1[[:digit:]]{2}99');
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '^1[[:digit:]]{2}99');
*/
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val, '^1[[:digit:]]{2}99$');
12399
4
--非純數字(具英文或標點符號)
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val,'^[[:digit:]]+$');--純數字
SELECT * FROM T0504 WHERE NOT REGEXP_LIKE(Val,'^[[:digit:]]+$');
12399
112999
--------------
12,599
xxz
1XZ fff
5
--不含數字
SELECT * FROM T0504 WHERE REGEXP_LIKE(Val,'^[^[:digit:]]+$');
xxz
6
--字首為12或者1x (不分大小寫)
SELECT * FROM T0504 WHERE REGEXP_LIKE(val,'^1[2x]','i');
12399
12,599
1XZ fff
7
--包含標點符號。
SELECT * FROM T0504 WHERE REGEXP_LIKE(val,'[[:punct:]]');

12,599
8
--包含小寫字母或者數字
SELECT * FROM T0504 WHERE REGEXP_LIKE(val,'^([a-z]+|[0-9]+)$');
12399
xxz
112999
9
--2碼相同
SELECT * FROM T0504 WHERE REGEXP_LIKE(val,'(.)\1');
12399
12,599
xxz
1XZ fff
112999
10
--3碼相同
SELECT * FROM T0504 WHERE REGEXP_LIKE(val,'(.)(\1)\2');
1XZ fff
112999


REGEXP_LIKE函數外ORACLE支援正規表示式函數如下表所列應用概念LIKEINSTRSUBSTR REPLACE 函數雷同,另外尚支援REGEXP_COUNT指令但由於與COUNT指令稍概念上稍有不同後續將再做說明 
#
函數
相似功能
類別
說明
1
LIKE
Condition
以正規則表模式(Pattern) 字串找出符合資料列(Row)應用於WHERE條件中
2
REPLACE
Function
於字串中使用正規則表式模式搜尋以指定字串取代一個符合模式之子字串
3
INSTR
Function
在字串中使用正規則表式模式搜尋,並傳回符子字串開始位置或結尾位置(整數)
4
SUBSTR
Function
在字串中使用正規則表式模式搜尋,傳回從字串中擷取

測試資料建立(使用INSERT ALL)
create table T0504
(
  Cate       NUMBER(4),
  Val        varchar(10)
);

INSERT ALL
INTO T0504 VALUES(1,  '12399')
INTO T0504 VALUES(2,  '12,599')
INTO T0504 VALUES(3,  'xxz')
INTO T0504 VALUES(4,  '1XZ fff')
INTO T0504 VALUES(5,  '112999')
SELECT * FROM DUAL;

沒有留言:

張貼留言