Remove Email Ids from a Strings

Question

I have a string with multiple email and need to only remove the email address from the string. Tried using the regexp but not exactly sure how to keep the other text but only remove the emails

David Ram:DRAM@info.au,Seja:SEJA@info.au,Yogan:YSGAN@info.au

Query:

 SELECT REGEXP_REPLACE(TEXTAREA,':[a-zA-z0-9.]@[a-zA-z0-9.]*' )  FROM CONTEXT

Output:

David Ram,Seja,Yogan

Please clarify if all emails have same domain. Also, are those emails some kind of local emails, issued by some company rules? Because if not - local part of email [can contain more symbols than just digits, letters and dots](https://stackoverflow.com/a/2049510/21363224) — markalex, May 09 '23 at 20:46

Littlefoot · Accepted Answer · 2023-05-09T20:42:12.800

3

Here's one option:

SQL> with test(col) as
  2    (select 'David Ram:DRAM@info.au,Seja:SEJA@info.au,Yogan:YSGAN@info.au' from dual)
  3  select regexp_replace(col, ':\w+@\w+\.\w+') result
  4  from test;

RESULT
--------------------
David Ram,Seja,Yogan

SQL>

edited May 09 '23 at 20:42

answered May 09 '23 at 20:14

Littlefoot

131,892
15
35
57

1

I believe you have missed ``\`` before `.` in regex (or was it intentional?) Also, this will work for example supplied in question, but will produce undesired output for emails with domain of more then two levels, like `yourquestions@bbc.co.uk`. – markalex May 09 '23 at 20:39
1

And also-also it will left any email with `.` in local part, like `name.surname@mail.com` – markalex May 09 '23 at 20:42
1

Yes, I missed \, thank you (fixed), @markalex. As of *undesired output*, well ... that's sample data OP provided, I don't know what they have (or don't have). – Littlefoot May 09 '23 at 20:42

score 1 · Answer 2 · answered May 09 '23 at 20:30

1

demo

SELECT REGEXP_REPLACE(TEXTAREA,':(.*?),',',' )  
FROM (SELECT 'David Ram:DRAM@info.au,Seja:SEJA@info.au,Yogan:YSGAN@info.au'||',' TEXTAREA from dual)

answered May 09 '23 at 20:30

xQbert

34,733
2
41
62

Remove Email Ids from a Strings

2 Answers2