clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798802 fiddles created (41850 in the last week).

CREATE TABLE regexp_test (my_email_string VARCHAR(255));
 hidden batch(es)


INSERT INTO regexp_test VALUES ('as.dfWEWd.fs+4@stuff.com'), ('aS.Sdf.34343a.sfs@yahoo.com'), ('adsFFdsf"£"££$£$+15@tester.cn'), ('test_dots.asdf.+12345@blah.au.com') -- multiple dots after the @ sign -- this is the tricky part!
4 rows affected
 hidden batch(es)


SELECT REPLACE(SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1), '.', '') || '@' || substring(my_email_string, '[^@]*$') FROM regexp_test;
?column?
asdfWEWdfs@stuff.com
aSSdf34343asfs@yahoo.com
adsFFdsf"£"££$£$@tester.cn
test_dotsasdf@blah.au.com
 hidden batch(es)


SELECT SPLIT_PART(my_email_string, '@', 1) FROM regexp_test;
split_part
as.dfWEWd.fs+4
aS.Sdf.34343a.sfs
adsFFdsf"£"££$£$+15
test_dots.asdf.+12345
 hidden batch(es)


SELECT LOWER ( REGEXP_REPLACE ( REPLACE ( SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1) , '.', '' ) || '@' || SUBSTRING(my_email_string, '[^@]*$'), '[^a-zA-Z0-9@.]', '', 'g' ) ) AS "New email" FROM regexp_test;
New email
asdfwewdfs@stuff.com
assdf34343asfs@yahoo.com
adsffdsf@tester.cn
testdotsasdf@blah.au.com
 hidden batch(es)