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

Question

Hi Developers,

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

my code :

DECLARE @USErId AS VARCHAR(100)='[email protected]'
DECLARE @USErId AS VARCHAR(100)='[email protected]'

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 [email protected] means outpout will be Original Owners= paulraj

if i give [email protected] 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

Vishal Joshi · Answer

Hello Please try below query to get the expected result. you can add replace character as per your requirement and you will get orinal owaner name. DECLARE @USErId AS VARCHAR(100)='paulraj@gmail.com' SELECT REPLACE(REPLACE(REPLACE(@USErId,SUBSTRING (@USErId,CHARINDEX( '@', @USErId ) + 1, LEN(@USErId)),''),'@',''),'.','') Thanks

Amit Mohanty · Answer

Try this DECLARE @email VARCHAR(50) = 'paul.raj12@gmail.com' DECLARE @numRange AS VARCHAR(50) = '%[0-9]%' DECLARE @owner VARCHAR(50) = @email WHILE PATINDEX(@numRange, @owner) > 0 SET @owner = STUFF(@owner, PATINDEX(@numRange, @owner), 1, '') PRINT @owner SELECT @email AS 'Original Owner Email', dbo.RemoveSpecialChars(LEFT(@owner, CHARINDEX('@', @owner) - 1)) AS 'Original Owner' Function: CREATE FUNCTION [dbo].[RemoveSpecialChars](@Input NVARCHAR(1000)) RETURNS NVARCHAR(1000) AS BEGIN DECLARE @KeepValues AS VARCHAR(50) SET @KeepValues = '%[^a-z0-9/]%' WHILE PATINDEX(@KeepValues, @Input) > 0 SET @Input = STUFF(@Input, PATINDEX(@KeepValues, @Input), 1, '') RETURN @Input END

Deepak Tewatia · Answer

Hi Paul, To get the email id value without any special characters or letters, you can use the below query: DECLARE @USErId AS VARCHAR(100) = 'paul.raj12@gmail.com'; SELECT DISTINCT SUBSTRING(@USErId, 1, CHARINDEX('@', @USErId)-1) AS 'Original Owners' FROM your_table; above query will extract the part of the email id before the @ symbol and return it as the Original Owners column. If you want to remove any special characters or letters from the email id, you can use the REPLACE function to replace them with an empty string: SELECT DISTINCT REPLACE(REPLACE(REPLACE(SUBSTRING(@USErId, 1, CHARINDEX('@', @USErId)-1), '.', ''), '-', ''), '_', '') AS 'Original Owners' FROM your_table; above query will remove all . , - , and _ characters from the email id before extracting it, you can change in above queries based on your requirement. Thanks

Paul Rajs · Answer

Hi Thanks for All of Your Quick and Correct Reply. Thanks a lot Amit Mohanty, Deepak Tewatia , Vishal Joshi and Aravind Govindaraj . Thanks and Regards, Paul.S

Aravind Govindaraj · Answer

Firstly set your defined special character in an array such as @,',- and etc., Then Check if your email address has any of these special characters, if exists then take the index of that special character. Get the Original Owner from index 0 to the special character index from the email address column.