Oracle SQL constraint to ensure data format-CodePudding

I'm trying to ensure that the data that goes into a cell/field in a data row follows the format YYYYAB, YYYYAC, YYYYAD. Meaning that any four number year and then the two characters "AB" or "AC" or "AD" are valid, anything else would be rejected.

Not sure how to compose the constraint with a like condition and the "_" or the "%" wildcards in order to accomplish this when I'm creating the column.

I was hoping to use something with a syntax like:

constraint cksemester check (SEMESTER in ( _ _ _ _ A B, _ _ _ _ A C, _ _ _ _ A D)),

or a combination of % and Regex..... is there a way to restrict the format to essentially any four numbers and then force the suffix to be any of "AB" or "AC" or "AD" ?

Thank you.

CodePudding user response：

You can use a REGEX to validate your field. A check constraint works like a where condition, so:

ALTER TABLE yourTable
  add constraint cksemester 
  check (REGEXP_LIKE(SEMESTER,'[[:digit:]]{4}(AB|AC|AD)','I'));

This regex ensures:

[[:digit:]]{4} : Exact four digits
(AB|AC|AD) : either AB or AC or AD
the 'I' parameter stands for case insensitive

CodePudding user response：

You can do it without regular expressions using the TRANSLATE function:

CONSTRAINT cksemester CHECK (
  TRANSLATE(
    semester,
    '0123456789',
    '0000000000'
  ) IN ('0000AB', '0000AC', '0000AD')
)

If you want to use regular expressions (which typically execute slower than simple string functions, such as TRANSLATE) then you can use:

CONSTRAINT cksemester CHECK ( REGEXP_LIKE(semester, '^\d{4}A[BCD]$') )