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