How to replace all the dots before @ in an email with empty string in Oracle SQL?

Question

I want to replace all the dots before @ in an email with empty string in oracle query

like:

anurag.mart@hotmail.com >> anuragmart@hotmail.com

Welcome to Stack Overflow. Take a [**tour**](http://stackoverflow.com/tour) to understand how this site works! — Lalit Kumar B, Nov 28 '15 at 13:36
I'm not sure why you'd want to do this, but be warned that [only Google ignores periods in the local part of an email address](http://stackoverflow.com/questions/14865869/do-all-email-providers-ignore-periods-in-front-of). Hotmail probably treats `anurag.mart@hotmail.com` and `anuragmart@hotmail.com` as different addresses, and per the RFC they are. — Bacon Bits, Nov 28 '15 at 14:29

Lalit Kumar B · Answer 1 · 2015-11-28T13:27:38.090

3

The easiest way is to use REGEXP_REPLACE to identify the pattern and replace it with required pattern.

regexp_replace('anurag.mart@hotmail.com', '(\w+)\.(\w+)(@+)', '\1\2\3')

For example,

SQL> SELECT 'anurag.mart@hotmail.com' email_id,
  2  regexp_replace('anurag.mart@hotmail.com', '(\w+)\.(\w+)(@+)', '\1\2\3') new_email_id
  3  FROM dual;

EMAIL_ID                NEW_EMAIL_ID
----------------------- ----------------------
anurag.mart@hotmail.com anuragmart@hotmail.com

edited Nov 28 '15 at 13:27

answered Nov 28 '15 at 13:12

Lalit Kumar B

47,486
13
97
124

This code will not work for long emails such as `Mohammad.bin.Salman.Al.Saud@gmail.com` – Egor Skriptunoff Nov 29 '15 at 18:06

Pரதீப் · Accepted Answer · 2015-11-28T13:28:17.843

Instr - To identify the position(@)
Substr - To extract data between start(1) and end(@) position
Replace - To replace . with ''
|| - To concatenate two strings

Try this

SELECT Replace(Substr('anurag.mart@hotmail.com', 1, 
                      Instr('anurag.mart@hotmail.com', '@', 1)), '.', '') 
       || Substr('anurag.mart@hotmail.com', Instr('anurag.mart@hotmail.com','@')+1) 
FROM   dual

Result:

anuragmart@hotmail.com

SqlFiddle Demo

score 0 · Answer 3 · answered Dec 07 '21 at 23:07

I came on this page while looking for solutions for SQL servers, I converted the above for SQL server for my project, Here is SQL if anybody else needs it.

SELECT 
    CONCAT(
        REPLACE(
            SUBSTRING(EmailAddress, 1, CHARINDEX('@', EmailAddress)-1),
            '.',
            ''
        ), 
        SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress), LEN(EmailAddress)) 
    )
FROM [Applicant]

How to replace all the dots before @ in an email with empty string in Oracle SQL?

3 Answers3