CODEWITHSUNDEEP
sqloracle-database
Anurag Bijalwan
Anurag Bijalwan

Reputation: 31

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

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

like: 

[email protected] >> [email protected]

Upvotes: 2

Views: 729

Answers (3)

Developer
Developer

Reputation: 26243

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]

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

  • 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('[email protected]', 1, 
                      Instr('[email protected]', '@', 1)), '.', '') 
       || Substr('[email protected]', Instr('[email protected]','@')+1) 
FROM   dual

Result:

[email protected]

SqlFiddle Demo

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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

regexp_replace('[email protected]', '(\w+)\.(\w+)(@+)', '\1\2\3')

For example,

SQL> SELECT '[email protected]' email_id,
  2  regexp_replace('[email protected]', '(\w+)\.(\w+)(@+)', '\1\2\3') new_email_id
  3  FROM dual;

EMAIL_ID                NEW_EMAIL_ID
----------------------- ----------------------
[email protected] [email protected]

Upvotes: 3

Related Questions