Oracle截取电子信箱问题？

Question

如何，将数据库的电子数据截取呢。如：[email protected]  只要[email protected] 只要absss
谢谢

Answer 1

with tmp as
(
select '[email protected]' email from dual union all
select '[email protected]' email from dual union all
select '[email protected]' email from dual
)
select regexp_replace(email, '(\w+)@(\w+\.)+\w{2,3}', '\1') result
from tmp;RESULT                                                                          
-------zb                                                                              
test                                                                            
absss 

Answer 2

with tmp as
(
select '[email protected]' email from dual union all
select '[email protected]' email from dual union all
select '[email protected]' email from dual
)
select regexp_replace(email, '(\w+)@(\w+\.)+\w{2,3}', '\1') result
from tmp;RESULT                                                                          
--------------
zb                                                                              
test                                                                            
absss 

Answer 3

还在嘛？
如果，说是表中有一列数据都是@163.com结尾的情况下，我想在写sql语句的时候可不可以将当前的着一列数据信息截取呢！！
比如:
select id from ty_nc_tw_mapping  a where a.regexp_replace(email, '(\w+)@(\w+\.)+\w{2,3}', '\1') ='zb'是否可以呢

Answer 4

可以的with tmp as
(
select '[email protected]' email from dual union all
select '[email protected]' email from dual union all
select '[email protected]' email from dual union all
select '[email protected]' email from dual
)
select email
from tmp
where  regexp_replace(email, '(\w+)@(\w+\.)+\w{2,3}', '\1')  = 'zb';EMAIL                
---------------------
[email protected]   

Answer 5

regexp_replace可以放在where中使用的，所以可以截取后再与传进来的前缀比较