Cannot create regexp check constraint

[GoogleCodeExporter]

Hi, I am trying to create a table with regexp check constraint using this 
script:

CREATE TABLE region (
    id INTEGER NOT NULL IDENTITY,
    name VARCHAR(255) NOT NULL,
    iso_code VARCHAR(6)
);
ALTER TABLE region
    ADD CONSTRAINT CHK_region_iso_code
        CHECK (iso_code REGEXP '^[A..Z]{2}(-[A..Z]{1-3})?$');

However, I am getting this error:

Error in LIKE ESCAPE: "^[A..Z]{2}(-[A..Z]{1-3})?$"; SQL statement:
ALTER TABLE region
    ADD CONSTRAINT CHK_region_iso_code
        CHECK (iso_code REGEXP '^[A..Z]{2}(-[A..Z]{1-3})?$')
[22025-132]

The regexp pattern is not using escape char, so I think it's a defect.

Original issue reported on code.google.com by jaroslav.kuruc on 15 Apr 2010 at 9:53

[GoogleCodeExporter]

Hi,

If it's a defect, it's a defect in Java pattern matching.

CALL 'x' REGEXP '^[A..Z]{2}(-[A..Z]{1-3})?$'

Caused by: java.util.regex.PatternSyntaxException: Unclosed counted closure 
near index 20

^[A..Z]{2}(-[A..Z]{1-3})?$
                    ^

Therefore, I resolve the bug as invalid. Please add a comment if you think 
that's wrong.

Original comment by thomas.t...@gmail.com on 17 Apr 2010 at 5:17

• Changed state: Invalid

[GoogleCodeExporter]

You are right. It was my mistake, the regexp was invalid. However, the error 
message 
could be improved to be less confusing. Thanks.

Original comment by jaroslav.kuruc on 17 Apr 2010 at 8:11