wsjrug
Last Updated: February 25, 2016
·
2.198K
· willl
57fdba7a233725ae0742e22a3c8b9dae

Replace email domain with example.com in SQL

Just a little snippet which updates emails.

DECLARE @var varchar(50)
SET @var = 'abc@exe.com';

SELECT 
    @var as 'Original', 
    SUBSTRING(@var, CHARINDEX('@', @var), LEN(@var) - CHARINDEX(@var, '@')) as 'ToBeReplaced', 
    REPLACE(@var, SUBSTRING(@var, CHARINDEX('@', @var), LEN(@var) - CHARINDEX(@var, '@')), '@example.com') as 'Replacement';

You can use this in an update statement...

Eg:

UPDATE MyTable
SET Email = REPLACE(MyTable.Email, SUBSTRING(MyTable.Email, CHARINDEX('@', MyTable.Email), LEN(MyTable.Email) - CHARINDEX(MyTable.Email, '@')), '@example.com')
WHERE MyTable.Email LIKE '%criteria%';
Say Thanks
Respond