CODEWITHSUNDEEP
regexpostgresql
Maks.Burkov
Maks.Burkov

Reputation: 626

How to compare regular expressions correctly in postgreSQL?

Trying to compare regular expressions from mine database to function parameters and got the following false result .. But if i go to https://regexr.com/ and try to compare with the same regular expression and the nickname i got match ..

Need your help , what i am doing wrong ?

select nickname_r from regex; // Here the regex that below

select '([0-9a-zA-Z.-_\=+\@]{2,15})' ~ 'Maks.+'; // RESULT false

set search_path = "postgres",coupon_system;
create or replace function loginValidator(nickname varchar, email varchar, u_password varchar) returns boolean as $$
    DECLARE
        checked boolean := false; n_regex varchar; e_regex varchar; p_regex varchar;
    BEGIN 

      select nickname_r , email_r, password_r into n_regex, e_regex, p_regex from regex;
      IF n_regex ~ nickname AND e_regex ~ email AND p_regex ~ u_password 
      THEN checked := true; 
      END IF;

     return checked;
    END;
$$ language plpgsql;

select loginValidator('Maks.+','[email protected]','+_Maks1988');

select nickname_r from regex;

select '([0-9a-zA-Z\.\-\_\=\+\@]{2,15})' ~ 'Maks.+'; // RESULT false

Upvotes: 1

Views: 82

Answers (1)

klin
klin

Reputation: 121604

A pattern should be on the right side of the operator:

select 'Maks.+' ~ '([0-9a-zA-Z\.\-\_\=\+\@]{2,15})'; 
-- yields true

Upvotes: 1

Related Questions