CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

How to verify valid email address in PostgreSQL?

I have a table with email field and like 30K records.. I discovered that many users has entered invalid email addresses with chars like : <,@@ etc...

Is there a way in PostgreSQL to run a query which will shows all invalid email address? I know how to do that with regular expressions

[a-z,A-z,0-9,'.']*@[a-z,A-z,0-9]*.com

[a-z,A-z,0-9,'.']*@[a-z,A-z,0-9]*.xx|xxx.xx for address like .co.uk or .org.it

How can this be done in a query? I just want to get a list of all invalid address so I can ask to fix their records.

Upvotes: 2

Views: 6126

Answers (1)

Georgi Raychev
Georgi Raychev

Reputation: 1334

You can use the POSIX regexp functionality of PostgreSQL:

Have a query with the following condition (email is your email column) in your where clause:

where email !~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'

Or whatever regular expression works for you.

Upvotes: 6

Related Questions