Sebastian Proske Sebastian Proske - 1 year ago 61
SQL Question

Firebird 2.5 - check contraint allowing only certain characters

I'm trying to create a column in firebird (2.5) in a way, that it:


  • cannot be NULL

  • must be exactly 16 characters

  • can only contain numbers and lowercase letters
    a
    -
    f



I could solve the first and second restriction.

ALTER TABLE TEST_TABLE ADD NEW_COLUMN CHAR(16) NOT NULL CHECK (CHAR_LENGTH(TRIM(VALUE)) = 16)


I would prefer to not use triggers for the third requirement, but a check constraint - is there any way to do this?

Answer Source

Can you use SQL regexes?

CHECK (value SIMILAR TO '[a-z0-9]{16}')

These were introduced in Firebird 2.5, and, conveniently for your purpose, must match the entire string. (That is, they're "anchored" at both ends of the search space.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download