Oracle Regular Expression constraint

Question

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.

Using ALL CAPS is considered as SHOUTING in online conversation. Please edit your title so it's not as loud. Thanks! — Mureinik
– Mureinik, Commented Mar 17, 2016 at 18:29

score 2 · Accepted Answer · 2016-03-17 19:58:31Z

2

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.

edited Mar 17, 2016 at 19:58

answered Mar 17, 2016 at 18:34

user2705585

Sign up to request clarification or add additional context in comments.

2 Comments

R Smoove Over a year ago

Works perfectly. Thank you. So if I'm understanding correctly, the ^ and the $ make it so no other characters can be added before or after the set, and the + ensures no unapproved characters can be in the middle of a set of approved characters? Such as "MJF" for example?

user2705585 Over a year ago

@RSmoove: Added explanation.

Collectives™ on Stack Overflow

Oracle Regular Expression constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related