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

Paul Rajs

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi3y ago
Accepted 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)='[email protected]'


SELECT REPLACE(REPLACE(REPLACE(@USErId,SUBSTRING (@USErId,CHARINDEX( '@', @USErId ) + 1, LEN(@USErId)),''),'@',''),'.','')
```
Thanks
Paul Rajs3y ago
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

Amit Mohanty3y ago

Try this

DECLARE @email VARCHAR(50) = '[email protected]'
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

Aravind Govindaraj3y ago
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.
Deepak Tewatia3y ago
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) = '[email protected]';

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