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

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