Thread: regexp match in plpgsql

V8.3.4 on linux

How does one do a regexp match/test in PlPgsql given a text variable containing a regexp and another text variable containt the string to test.  Example that shows what I'm trying to do...

declare

 rgxp text;

 str1 text;

 str2 text;

begin

  rgxp := '[a-zA-Z0-9]';

  str1 := 'ShouldBeOK99';

  str2 := 'Should_Fail_match77';

  if(str1 =~ E\rgxp\) then     raise notice '% is a match',str1;     else    raise notice '% is not a match',str1;    end if;

  if(str2 =~ E\rgxp\) then     raise notice '% is a match',str2;     else    raise notice '% is not a match',str2;    end if;

end;

$$ language plpgsql

I would expect to see...

"ShouldBeOK99 is a match"

"Should_Fail_match77 is not a match"

Thanks in Advance.

You are trying to check the entire string to ensure only the specified character class matches at each position.  What you are doing is seeing whether or not there is at least one character class matching value in the tested string.

Since you want to check the entire string you should:

Anchor it using “^” and “$”

Repeat the test: [a-zA-Z0-9]+ ; “+” so we do not match the empty-string

Thus: ^[a-zA-Z0-9]+$

As for the actual question, how, I am unsure but if the issue was (and you never did say what output or errors you are getting) that you were getting “true” for both tests then this is the reason and the solution.

David J.

V8.3.4 on linux

How does one do a regexp match/test in PlPgsql given a text variable containing a regexp and another text variable containt the string to test.  Example that shows what I'm trying to do...

declare

 rgxp text;

 str1 text;

 str2 text;

begin

  rgxp := '[a-zA-Z0-9]';

  str1 := 'ShouldBeOK99';

  str2 := 'Should_Fail_match77';

  if(str1 =~ E\rgxp\) then     raise notice '% is a match',str1;     else    raise notice '% is not a match',str1;    end if;

  if(str2 =~ E\rgxp\) then     raise notice '% is a match',str2;     else    raise notice '% is not a match',str2;    end if;

end;

$$ language plpgsql

I would expect to see...

"ShouldBeOK99 is a match"

"Should_Fail_match77 is not a match"

Thanks in Advance.

Yup, I forgot the "^". 

Works now.

Thanks !

You are trying to check the entire string to ensure only the specified character class matches at each position.  What you are doing is seeing whether or not there is at least one character class matching value in the tested string.

Since you want to check the entire string you should:

Anchor it using “^” and “$”

Repeat the test: [a-zA-Z0-9]+ ; “+” so we do not match the empty-string

Thus: ^[a-zA-Z0-9]+$

As for the actual question, how, I am unsure but if the issue was (and you never did say what output or errors you are getting) that you were getting “true” for both tests then this is the reason and the solution.

David J.

V8.3.4 on linux

How does one do a regexp match/test in PlPgsql given a text variable containing a regexp and another text variable containt the string to test.  Example that shows what I'm trying to do...

declare

 rgxp text;

 str1 text;

 str2 text;

begin

  rgxp := '[a-zA-Z0-9]';

  str1 := 'ShouldBeOK99';

  str2 := 'Should_Fail_match77';

  if(str1 =~ E\rgxp\) then     raise notice '% is a match',str1;     else    raise notice '% is not a match',str1;    end if;

  if(str2 =~ E\rgxp\) then     raise notice '% is a match',str2;     else    raise notice '% is not a match',str2;    end if;

end;

$$ language plpgsql

I would expect to see...

"ShouldBeOK99 is a match"

"Should_Fail_match77 is not a match"

Thanks in Advance.