oracle 字串處理實踐

背景一：

人員資訊表，表中有email_address欄位，欄位裡面的值有如下的格式:

 name1@test.com.cn或者name2/test/com/cn

想得到人名字。

用字串處理函數如下：

代碼一：

select email_address
      --,instr(email_address,'@',1,1)
      --,decode(instr(email_address,'@'),0,'未加@',substr(email_address,instr(email_address,'@'),length(email_address))) 判斷
      ,replace(email_address
               ,substr(
                       email_address
                       ,decode(
                                 decode(
                                         instr(email_address,'@')
                                         ,0
                                         ,instr(email_address,'@')
                                         ,0
                                        )
                                  ,0
                                  ,instr(email_address,'/')
                                )
                       ,length(email_address)+1-decode(
                                                        decode(
                                                               instr(email_address,'@')
                                                               ,0
                                                               ,instr(email_address,'@'),0
                                                               )
                                                         ,0
                                                         ,instr(email_address,'/')
                                                         )
                       )
              )
   --   ,decode(decode(instr(email_address,'@'),0,instr(email_address,'@'),0),0,instr(email_address,'/'))
from ontact

--完畢 裡面有複雜的判斷主要用到decode進行判斷，分開始'@'還是'/'

 

處理二：

使用regexp_replace

 select email_address ,regexp_replace(email_address,'(@|/)[a-zA-Z./@]*') from ca_contact