Search code examples
databaseoracle-databaseconstraintsddlalter

How to i add a check constraint that only allows A-Z for the last character

Hi i have a varchar column which i want to only allow character from A-Z on the last character. I have tried multiple solutions but it didn't work. The last one i tried was 

ALTER TABLE timetableslot
ADD CONSTRAINT CHK_RoomNo CHECK (roomNo NOT LIKE '%[^A-Z]');

But i still can add values like asdd1 into it. Is there a way around this? Thank you in advance

Solution

  • You can use ASCII() function together with SUBSTR() :

    ALTER TABLE timetableslot
ADD CONSTRAINT CHK_RoomNo CHECK (ASCII(SUBSTR(roomNo,-1)) BETWEEN 65 AND 90);

    Demo

    where ASCII value for A is 65, for Z it is 90, and all other capitals stay in this range.