Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE regexp_test (my_email_string VARCHAR(255));

CREATE TABLE

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!

INSERT 0 4

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

SELECT 4

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

SELECT 4

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

SELECT 4