CODEWITHSUNDEEP
databaseoracle-databaseconstraintsddlalter
aalang
aalang

Reputation: 35

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

Upvotes: 1

Views: 869

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

regexp_like() is a simple solution:

ALTER TABLE timetableslot ADD CONSTRAINT
    CHK_RoomNo CHECK (REGEXP_LIKE(roomNo, '[A-Z]$') );

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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.

Upvotes: 2

Related Questions