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