Re: Regular expression in an if-statement will not work

I have discovered the reasons of my problem.

Firstly I made a mistake at the type declaration of the column "value". It was "character(3)". So that missing characters were filled with spaces and the regular expression in case of less than three digits couldn't match at the end ("$"). I changed the type to "character varying(3)". Not a really suitable solution, but it should be enough for testing. It works with all regular expressions like the following:

'^[0-9]{1,3}$' or '^[\x30-\x39]{1,3}$' or '^[\u0030-\u0039]{1,3}$'.

The second mistake I made with the escaping of characters. In my case four "\" are needed to write a regular expression with "\d". So '^\\\\d{1,3}$' works correct.

Thanks to all who gives me new ideas to solve the problem and kind regards,
Carsten Ungermann

Am 09.09.2010 um 17:52 schrieb Richard Huxton:

> On 09/09/10 11:55, Ungermann Carsten wrote:
>> Dear postgres list,
>>
>> I need to verify the value of a column by a regular expression in an
>> if-statement before insert or update. It should be a one to three digit
>> value.
>
>> '^[0-9]{1,3}$' -- don't work
>
> Works here.
>
> CREATE TEMP TABLE tt (t text);
> INSERT INTO tt VALUES ('1'),('12'),('123'),('1234'),(' 123'),('123 ');
> SELECT ':' || t || ':' AS target, t ~ '^[0-9]{1,3}$' FROM tt;
> target | ?column?
> --------+----------
> :1: | t
> :12: | t
> :123: | t
> :1234: | f
> : 123: | f
> :123 : | f
> (6 rows)
>
> Works in 8.2, 8.3, 8.4, 9.0 for me. Either you're not testing the values you think you are, or there is some issue with escaping of characters.
>
> --
> Richard Huxton
> Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general