CODEWITHSUNDEEP
sqloracle-databaseconstraints
Rowend
Rowend

Reputation: 51

Check Constraint with letters and digits

I am trying to give a constraint to a column( varchar2(7) ) where I want to have an ID in this order Letter_Letter_Digit_Digit_Letter_Letter_Letter, so 2 letters, 2 digits, 3 letters. I've tried with something like this and several instructions :

ALTER TABLE vehicule 
ADD CONSTRAINT CHK_nr_vehicul 
CHECK (nr_vehicul LIKE '[A-Z][A-Z][0-9][0-9][A-Z][A-Z][A-Z]');

When I try to give something like this 'AB00BBB' my constraint is violated. Any ideas?

Thank you!

Upvotes: 1

Views: 1803

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use REGEP_LIKE() function such as

ALTER TABLE vehicule 
ADD CONSTRAINT chk_nr_vehicul 
CHECK ( REGEXP_LIKE (nr_vehicul,'[A-Z]{2}[0-9]{2}[A-Z]{3}') );

where the digits in the curly braces represent the length for each part for substrings forming the whole string of length 7.

Update : If you need to insert the values with some other patterns such as AB00BB and B000BBB along with the pattern of current sample value(AB00BBB), then use pipe oprators such as

ALTER TABLE vehicule 
ADD CONSTRAINT chk_nr_vehicul 
CHECK ( REGEXP_LIKE (nr_vehicul,'[A-Z]{2}[0-9]{2}[A-Z]{3}|[A-Z]{2}[0-9]{2}[A-Z]{2}|[A-Z]{1}[0-9]{3}[A-Z]{3}') );

Upvotes: 3

Related Questions