CODEWITHSUNDEEP
sqlregexoracle-databasecheck-constraints
AnsonH
AnsonH

Reputation: 3256

Oracle SQL - REGEXP_LIKE not working as expected in CHECK constraint

I'm using Oracle SQL and I want to add a CHECK constraint that checks a regular expression when I create a table. However, the REGEXP_LIKE function behaves differently from online RegEx testing tools.

Suppose I want to create a table called Testing with only one attribute called roomNo. I want roomNo to only store 4 numerical digits optionally followed by an uppercase letter (eg. 1234, 1234A):

CREATE TABLE Testing (
    roomNo      char(5),
    CONSTRAINT check_roomNo CHECK (REGEXP_LIKE(roomNo, '^\d{4}[A-Z]?$'))
);
insert into Testing values ('1234X');
insert into Testing values ('1234');  -- Unexpected error here

When I run this script, I can successfully insert 1234X into the table but Oracle SQL throws me an error when inserting 1234:

Error starting at line : 5 in command -
insert into Testing values ('1234')
Error report -
ORA-02290: check constraint (USER.CHECK_ROOMNO) violated

I don't quite understand why this is an error since when I test the RegEx ^\d{4}[A-Z]?$ in an online RegEx testing tool, it can match both 1234X and 1234. Here is a link to a RegEx testing tool that uses the RegEx I used in the REGEXP_LIKE function. Thank you very much!

Upvotes: 1

Views: 594

Answers (1)

AnsonH
AnsonH

Reputation: 3256

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]?$'))

Upvotes: 3

Related Questions