1

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
aalang
  • 35
  • 3
  • Have you seen: [Oracle 11g - Check constraint with RegEx](https://stackoverflow.com/questions/7621568/oracle-11g-check-constraint-with-regex) ? – Abra Jan 07 '20 at 19:30

2 Answers2

2

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Why `ASCII()`? Why not just use `CHECK ( SUBSTR(roomNo,-1) BETWEEN 'A' AND 'Z' )`? Then everyone looking at the check constraint knows what it was intended to do and doesn't have to go and consult an ASCII table to check if 90 is `Z` or `[`. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=4c61d4e08de945dd57d4a42d23ff5eb9) – MT0 Jan 07 '20 at 20:24
  • Yes, that might be as another option @MT0 . – Barbaros Özhan Jan 07 '20 at 20:41
1

regexp_like() is a simple solution:

ALTER TABLE timetableslot ADD CONSTRAINT
    CHK_RoomNo CHECK (REGEXP_LIKE(roomNo, '[A-Z]$') );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks this works, does the dollar sign means last character? what if i want to check the second last character, would it be $$? – aalang Jan 07 '20 at 19:43
  • @aalang perhaps a [tutorial](https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_regexp.htm) is appropriate? – Abra Jan 07 '20 at 19:50