CODEWITHSUNDEEP
sqloracle-database
Bendemann
Bendemann

Reputation: 776

CHECK constraint for string to contain a certain amount of digits as well as certain digits. (Oracle SQL)

I have a column, number where I need a length constraint (say 11 digits) as well as to assert the existence of some certain numbers. Let us say the first four digits need to be '1234' and the fifth in the range'6-9'. I am using a varchar type so I also need to assert numbers. With some research here is what I have been able to come up with:

CHECK (REGEXP_LIKE(number, '^1234\d{6}$'))

In this way I have been able to check the number of digits (11), the first 4 starting numbers and number values. However, I cannot fit the fifth number which needs to be between 6 and 9 into this expression. Thanks in advance

Upvotes: 0

Views: 61

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31646

Try this.

CHECK (REGEXP_LIKE(number, '^1234[6-9]\d{6}$'))

Upvotes: 3

Related Questions