CODEWITHSUNDEEP
sqlconstraintsoracle12csql-like
Effectsmeister
Effectsmeister

Reputation: 15

Oracle SQL constraint to ensure data format

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.

Upvotes: 0

Views: 83

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

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

Edit:

As suggested by MT0 in the comments, a better version of the above regex is ^[[:digit:]]{4}(AB|AC|AD)$

  • ^ ensures the string needs to start there, from the 4 digits
  • $ ensures the string finishes there, it need to end after the "or" expression.

This improved regex, prevent "outbounds" of the original one such as XYZ1234ABCDEF which would be considered valid and it is not.

Upvotes: 1

MT0
MT0

Reputation: 168361

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

Upvotes: 2

Related Questions