Oracle 11G - Check Constraint with Regex

Oracle SQL - REGEXP_LIKE not working as expected in CHECK constraint

After some digging, I found that roomNo having the data type of char(5) is the culprit of this problem.

According to Oracle documentation on char in here:

When you insert a character value into a CHAR database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width

When I insert 1234 into the table, since 1234 has a shorter length than the defined width of the column (ie. width of 5), Oracle adds blank pads to the right of the string so that the string is stored as 1234 with a single blank space after 1234.

Therefore, the string 1234 fails to match the RegEx ^\d{4}[A-Z]?$ since the last character of the string (ie. a blank space) does not match [A-Z].

A solution I can come up with is to use the RegEx of ^\d{4}[A-Z ]?$ so that it can match the single blank space located at the 5th character.

Edit:

Another solution is to use RTRIM() function to remove tailing whitespaces at the right:

CONSTRAINT check_roomNo CHECK (REGEXP_LIKE(RTRIM(roomNo), '^\d{4}[A-Z]?$'))

Oracle check constraint to enforce only numbers and full stops

Try something like

alter table TBL add constraint TBL_COL_FMT
  check (regexp_like(COL, '^\d+(\.\d+)*$'));

where the regular expression means one or more digits (\d+) followed
by zero or more groups of a single dot and one or more digits ((\.\d+)*).

Oracle constrain with regexp_like

Try the following regex: ^[A-Za-z()&| ).]*$

For more details: https://regex101.com/r/uH1yL9/1

[A-z] is not the same as [A-Za-z]

Related Topics