| email |
+---------------------------------+
| abcd@gmail.com |
| bdef@gmail.com abdfgr@yahoo.com |
+---------------------------------+
我尝试了以下,它正在为第一行工作
substr(email,instr(email,'@',1,1))
但是,对于同一单元格上的第二行,我们有2个域ID。现在,如何检索下方的输出
gmail.com
gmail.com
yahoo.com
例如:
SQL> with test (email) as
2 (select 'abcd@gmail.com' from dual union
3 select 'bdef@gmail.com abdfgr@yahoo.com' from dual
4 )
5 select ltrim(regexp_substr(email, '@(w+.w+)', 1, column_value), '@') res
6 from test,
7 table(cast(multiset(select level from dual
8 connect by level <= regexp_count(email, '@' )
9 ) as sys.odcinumberlist));
RES
-----------------------------------------------------------------------------------
gmail.com
gmail.com
yahoo.com
SQL>
您可以使用Regexp_substr,例如:
SELECT REGEXP_SUBSTR ('jlora@outlook.com', '(@).*') from DUAL
结果: @outlook.com
您可能会尝试以下内容。我假设您的表上有一个主键列或列,其中还包含电子邮件列:
SELECT id, TRIM('@' FROM REGEXP_SUBSTR(email, '@S+', 1, LEVEL))
FROM mytable
CONNECT BY REGEXP_SUBSTR(email, '@S+', 1, LEVEL) IS NOT NULL
-- If you have a composite key, mention all of the columns here:
AND PRIOR id = id
AND PRIOR SYS_GUID() IS NOT NULL;
请参阅此处的SQL小提琴(忽略架构!(。
现在,以上查询将获得所有结果,包括重复项,因此,如果email
列中的值如下:you@yahoo.com you@gmail.com you@gmail.com
,则gmail.com
将以该主要键值返回两次。只需用SELECT DISTINCT
替换SELECT
即可解决该问题(如果您想解决(。