CODEWITHSUNDEEP
regexoracle-database
R Smoove
R Smoove

Reputation: 13

Oracle Regular Expression constraint

I currently have a constraint I am trying to build for an Oracle database column.

ALTER TABLE section ADD CONSTRAINT section_days_chk CHECK (Days = 'M' OR Days = 'T' OR Days = 'W' OR Days = 'R' OR Days = 'F' OR Days = 'S' OR Days = 'U');

However, I also need the constraint to allow for any combination of those characters as well.

I have been trying to figure out how to use a regular expression "like" constraint to do that without allowing any other characters. For example, only the characters M or T or W or R or F or S or U or any combination of the letters should be allowed in like "MWF" or "TR" or "M". I've been having trouble finding an example that will constrain the input like this.

This is what I've tried:

ALTER TABLE section ADD CONSTRAINT section_days_chk CHECK (regexp_like(Days,'[MTWRFSU]'));

But it allows any letters as long as they are attached to one included in the set like "MD".

Any ideas? I've been looking for a few days, and this task doesn't seem to be very common. All the examples I have found assume you are looking for words where a certain character is included in it.

Upvotes: 0

Views: 286

Answers (1)

user2705585
user2705585

Reputation:

Try using following one

REGEXP_LIKE (Days, '^[MTWRFSU]+$')

Explanation:

  • ^ and $ are anchors. They say that your string should be a match itself.

  • + mean one or many. So one or many characters from class [MTWRFSU] can come together. If any other character is added match will be discarded

Check REGEXP_LIKE documentation.

Upvotes: 2

Related Questions