是否有一种最简单的方法可以仅从Oracle中的电子邮件地址选择域名

Question

我有一个名为 email_id 的表格和列名称为 email_id ，我在列中有这些电子邮件ID

[e-mail]
[e-mail]
[e-mail]
[e-mail] 

我只需要这样的域名，例如 result

yahoo
msn
gmail
xmsoftware 

除此之外是否有最简单的方法（或）其他功能？

select substr(email_id.email_id,(instr(email_id.email_id,'@',1)+1),(instr(email_id.email_id,'.',1) - (instr(email_id.email_id,'@',1)+1))) as domain 
from email_id;

Answer 1

我更喜欢 REGEXP_SUBSTR

select REGEXP_SUBSTR(email_id,'^.*@(.*).(.*)$',1,1,null,1) as domain 
 FROM emails;--                     ^ first match        ^ within()

demo

Answer 2

老式方法是使用SUBSTR + INSTR组合。它需要更多字母来输入（与正则表达式相比），但在大型数据集上可能工作得更快。

SQL> WITH email_id (email_id)
  2       AS (SELECT '[e-mail]' FROM DUAL
  3           UNION ALL
  4           SELECT '[e-mail]' FROM DUAL
  5           UNION ALL
  6           SELECT '[e-mail]' FROM DUAL
  7           UNION ALL
  8           SELECT '[e-mail] ' FROM DUAL)
  9  select email_id,
 10         substr(email_id,
 11                instr(email_id, '@') + 1,
 12                instr(email_id, '.', instr(email_id, '@')) - instr(email_id, '@') - 1) result
 13  from email_id;
EMAIL_ID             RESULT
-------------------- --------------------
[e-mail]       yahoo
[e-mail]         msn
[e-mail]       gmail
[e-mail]  xmsoftware
SQL>