CODEWITHSUNDEEP
regexpostgresql
user1259962
user1259962

Reputation:

PostgreSQL regex to validate email addresses

CREATE OR REPLACE FUNCTION addUploader(INTEGER, TEXT, TEXT) RETURNS TEXT AS $$

DECLARE

  u_id ALIAS FOR $1 ;
  username ALIAS FOR $2;
  email ALIAS FOR $3; 

BEGIN 

  IF email NOT LIKE '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$' THEN

    RAISE EXCEPTION 'Wrong E-mail format %', email
        USING HINT = 'Please check your E-mail format.';

  END IF ; 

  INSERT INTO uploader VALUES(u_id,username,email);

  IF NOT FOUND THEN
    RETURN 'Error';
  END IF;
  RETURN 'Successfully added' ; 

EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'This ID already exists. Specify another one.' ; 
  RETURN 'Error' ; 

END ; $$ LANGUAGE 'plpgsql' ; 

SELECT addUploader(25,'test','[email protected]');

This regex does not accept a correct email address. It should accept [email protected] is also rejecting any other string.

stevengmailcom - rejected

Everything is being rejected.

What am i doing wrong?

Upvotes: 10

Views: 25994

Answers (3)

Scott Marlowe
Scott Marlowe

Reputation: 8870

Validating email addresses is fraught with peril. It's far easier to create a regex that refuses to accept valid email addresses than one that rejects invalid ones only. Here's the perl regex that's RFC822 compliant:

My advice is to accept anything, then use email to verify it works. If you MUST do validation then use the perl module listed on that page and pl/perl.

Upvotes: 5

Tim Pote
Tim Pote

Reputation: 28029

I think you want one of the tilde operators instead of like.

like has a different syntax (for instance it uses % instead of .*) consisting only of wildcards, and they must match the entire string, so anchors like ^ and $ can't be used with like. I personally think of it more like file globbing than actual pattern matching.

Tilde operators give you a robust regex syntax more akin to egrep, sed, and awk.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434616

You don't use LIKE with regexes in PostgreSQL, you use the ~, ~*, !~, and !~* operators:

~
Matches regular expression, case sensitive

~*
Matches regular expression, case insensitive

!~
Does not match regular expression, case sensitive

!~*
Does not match regular expression, case insensitive

So your test should look more like this:

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

You also might want to hunt down a better regex for email addresses, "[email protected]" is a valid email address but your regex doesn't like it.

Upvotes: 36

Related Questions