我想在一个字段中获取电子邮件格式的文本。我在下面尝试了sql，但是没有运气。参见SqlFiddle。从正则表达式中删除^和$也不起作用。
WITH TEST_DATA AS (
SELECT '[email protected]' AS EMAIL FROM DUAL UNION ALL
SELECT 'mail [email protected]' FROM DUAL UNION ALL
SELECT 'mail [email protected] sent' FROM DUAL UNION ALL
SELECT '[email protected] sent count 23' FROM DUAL UNION ALL
SELECT 'mail already sent to [email protected] and [email protected]' FROM DUAL UNION ALL
SELECT '[email protected] sent count 23' FROM DUAL
)SELECT REGEXP_SUBSTR(EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') MAIL
FROM TEST_DATA;
该数据集的预期输出
[email protected]
[email protected]
[email protected]
[email protected]
[email protected], [email protected]
[email protected]
任何帮助表示赞赏。
如果要在单个列中提取多个邮件ID，则可以使用REGEXP_REPLACE函数。
假设您数据中的所有ID均为有效ID，
REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')
这会删除所有其他文本，但邮件ID至少用空格隔开。
然后，您可以删除任何尾随空格并添加逗号以分隔多个ID。
REPLACE (TRIM (REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')),
' ',
', ')
例：
WITH TEST_DATA
AS (SELECT '[email protected]' AS EMAIL FROM DUAL
UNION ALL
SELECT 'mail [email protected]' FROM DUAL
UNION ALL
SELECT 'mail [email protected] sent to [email protected] and [email protected]' FROM DUAL
UNION ALL
SELECT '[email protected] sent count 23 and [email protected]' FROM DUAL
UNION ALL
SELECT 'mail already sent to [email protected] and [email protected]' FROM DUAL
UNION ALL
SELECT '[email protected] sent count 23' FROM DUAL)
SELECT REPLACE (TRIM (REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')),
' ',
', ')
MAIL
FROM TEST_DATA;
MAIL
-----------------------------
[email protected]
[email protected]
[email protected], [email protected], [email protected]
[email protected], [email protected]
[email protected], [email protected]
[email protected]
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句