oracle Regex 2 包括底線的處理__大資料

l        方括弧表達示

方括號運算式	描述
[[:alnum:]]	字母和數字混合的字元
[[:alpha:]]	字母字元
[[:cntrl:]]	控制字元
[[:digit:]]	數字字元
[[:graph:]]	映像字元
[[:lower:]]	小寫字母字元
[[:print:]]	列印字元
[[:punct：]]	標點符號字元
[[:space:]]	空白字元
[[:upper:]]	大寫字母字元
[[:xdigit:]]	十六進位數字字元

l        元字元

元字元	描述
*。	如果有前面字串的0次以上出現時匹配
+。	如果有前面字串的1次以上出現時匹配
{m}	m是整數。它文本中找出給定子運算式的恰好m次出現
{n}?	前面的字串只出現一次時匹配
{m,}	M是整數。它在文本中找出給定子表達示的至少m次出現
{n,}?	匹配前面的字串至少n次
{m,n}	M和n是整數。它在文本中找出給定子表達示的m到n次出現
{n,m}	匹配前面的字串至少到n次，但不多於m次
c	查詢操作區分大小寫
i	查詢操作不區分大水寫
m	多行字串上的查詢，在源字串包含多行時，該查詢允許用（^）模式比對字元串的開始
n	通常匹配單個字元，也可以匹配新行
x	需要忽略正則表達示中的空白字元時，使用參數‘X’
\A	匹配字串首，而不是行首，因而多行字串不能匹配每一行
\d	匹配任一數字字元
\D	匹配任意非數字字元
\s	匹配任意空白字元
\S	匹配任意非空白字元
\w	匹配任一字元和數字。該字元和[:alnum:]之間的不同是\w包括底線
\W	匹配任意非Null 字元串
*	萬用字元。找出在文本中包含0或多次給定子運算式出現的記錄
+	找出在文本中包含1或多次給定子運算式出現的記錄
。	找出在文本中包含0次或1次給定子表達示出現的記錄
.	匹配文本中的任一字元
^	錨。如果該字元後的運算式出現在行首，則匹配成功
$	錨。如果該字元後的運算式出現在行首，則匹配成功
|	分隔字元，使用方法和OR相同
(….)	分組子運算式

l        Regex運算子和函數

a)     REGEXP_SUBSTR

REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)  

__srcstr        ：檢索字串

__pattern      ：匹配模式

__position     ：搜尋srcstr的起始位置（預設為1）

__occurrence：搜尋第幾次出現匹配模式的字串（預設為1）

__modifier     ：檢索模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。預設為'c'。）  

eg:  

SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+') from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ 30637   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 1) from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ 30637   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 35) from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ 1009927   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 1, 2) from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ 1009927   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/showart_1009927.html', '[a-z_0-9]+', 35) from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ showart_1009927   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35) from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ howart_1009927   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35, 1, 'c') from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ howart_1009927   SQL> select regexp_substr(' http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35, 1, 'i') from dual; REGEXP_SUBSTR('HTTP://BLOG.CHI ------------------------------ Showart_1009927

b)     REGEXP_INSTR

REGEXP_INSTR返回與Regex匹配的字元和字串的位置。如

SQL> select regexp_instr('The zip code 80831 is for falcon, co', '[[:digit:]]{5}') REGEXP_INSTR from dual;

REGEXP_INSTR

------------

         14

c)     REGEXP_REPLACE

REGEXP_REPLACE與REPLACE函數類似，提供一種修改與所給Regex匹配的字串的方法。作用包括糾正拼字錯誤、格式化輸入輸出的文本。

如電話號碼的格式為：719-111-1111。使用REGEX_REPLACER的傳回值是：

SQL> select regexp_replace('Reformat the phone number 719-111-1111 ...',

2        '[1]?[-.]?(\(?[[:digit:]]{3}\)?)+[- .]?'

3        || '([[:digit:]]{3})[- .]?([[:digit:]]{4})',

4        ' (\1) \2-\3') regexp_replace

5   from dual;

REGEXP_REPLACE

---------------------------------------------

Reformat the phone number (719) 111-1111 ...

S

d)     REGEXP_LIKE

REGEXP_LIKE運算子與LIKE運算子相似，但是功能更強大，因為它支援使用與此Regex與文本進行匹配。文法如下：

REGEXP_LIKE(source_string, pattern, match_parameter)

Source_string可以是文字字串，如果前面例中的字串，也可以是包含某些字串的變數或列。Pattern是要進行匹配的Regex。Match_parameter用於指定在匹配時是否區分大小寫。

SQL> select ename, job

2   from emp

3  where regexp_like(job, '(clerk|analyst)', 'i');

ENAME     JOB

---------- ---------

SMITH     CLERK

SCOTT     ANALYST

ADAMS     CLERK

JAMES     CLERK

FORD      ANALYST

MILLER    CLERK

 

============================個人使用體會=======================================

 

select p.port_no,p.display_name ,  substr(p.port_iden,instr(p.port_iden,'/',-1,1) + 1)
from tb_port p where p.type_info = 'adsl'
 and REGEXP_LIKE(substr(p.port_iden,instr(p.port_iden,'/',-1,1) + 1),'^[:punct:]|[0-9]+$')
 and to_number(substr(p.port_iden,instr(p.port_iden,'/',-1,1) + 1))  <>  p.port_no and p.port_no = 0

1. 應用 REGEXP_LIKE(substr(p.port_iden,instr(p.port_iden,'/',-1,1) + 1),'^[:punct:]|[0-9]+$')  判斷字串中都是數字

2. substr(p.port_iden,instr(p.port_iden,'/',-1,1) + 1) 截取 字串中 包含 ‘/‘ 的，取最後一個斜杠後邊的字元

3. to_number 將字元數字轉成 number類型

 

INSTR

　　(源字串, 目標字串, 起始位置, 匹配序號)

　　在Oracle/PLSQL中，instr函數返回要截取的字串在源字串中的位置。只檢索一次，就是說從字元的開始

　　到字元的結尾就結束。

　　文法如下：

　　instr( string1, string2 [, start_position [, nth_appearance ] ] )

　　參數分析：

　　string1

　　源字串，要在此字串中尋找。

　　string2

　　要在string1中尋找的字串.

　　start_position

　　代表string1 的哪個位置開始尋找。此參數可選，如果省略預設為1. 字串索引從1開始。如果此參數為正，從左至右開始檢索，如果此參數為負，從右至左檢索，返回要尋找的字串在源字串中的開始索引。

　　nth_appearance

　　代表要尋找第幾次出現的string2. 此參數可選，如果省略，預設為 1.如果為負數系統會報錯。

　　注意：

　　如果String2在String1中沒有找到，instr函數返回0