email.sql

-- select syntactically-improbable email addresses (and a redacted form)
-- be tolerant of leading/trailing spaces as that seems common, and is
-- probably safe to remove in a batch update
SELECT email, regexp_replace(email, '[a-zA-Z0-9]', 'Z', 'g') as redacted
FROM actor.usr
WHERE home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND email !~ '^ *[a-zA-Z0-9\._+-]+@([a-zA-Z0-9-]+\.)+[a-zA-Z]{2,} *$'
AND email <> ''
ORDER BY reverse(lower(email));