Paul Rajs

Paul Rajs

  • 1.6k
  • 641
  • 119.5k

How to get a mail id value with or without . before @ symbol

Dec 21 2022 1:28 PM

Hi Developers,

I need to get the email id value without any special characters and letters.

my code : 

DECLARE @USErId AS VARCHAR(100)='paulraj@gmail.com'
DECLARE @USErId AS VARCHAR(100)='paul.raj12@gmail.com'

SELECT  
  DISTINCT 

  CASE WHEN ISNULL(@USErId,'') like '%gmail.com%' THEN SUBSTRING(@USErId, 1, CHARINDEX('.', @USErId)-1) ELSE   
  SUBSTRING(@USErId, 1, CHARINDEX('.', @USErId)-1) +REPLACE(SUBSTRING(SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000), 1, 
       CHARINDEX('@', SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000))-1),
       REVERSE(LEFT(reverse(SUBSTRING(SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000), 1, 
       CHARINDEX('@', SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000))-1)), 
       PATINDEX('%[0-9][^0-9]%', reverse(SUBSTRING(SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000), 1, 
       CHARINDEX('@', SUBSTRING(@USErId, CHARINDEX('.', @USErId) + 1, 1000))-1)) ))),'')
       end  AS 'Original Owners',
    CASE WHEN ISNULL(@USErId,'') like '%gmail.com%' THEN  ISNULL(@USErId,'') ELSE '' END AS 'OriginalOwnerEmail'

Expected Output : 

when i give paulraj@gmail.com means outpout will be Original Owners= paulraj

if i give paul.raj12@gmail.comoutpout will be Original Owners= paulraj

I have tried with google but i did not get exact output. If anyone know kindly suggest me to done this task.

Regards,

Paul.,S


Answers (5)