Adding constraint in the column oracle

Question

I am facing issue adding constraint to the column of table in oracle- The column is defined with the datatype char(500 char). I need to put the constraint which allows only the digits and N/A value to be inserted in the column.

Please edit the question with, what you tried and the issue that you are facing — Noel
– Noel, Commented Feb 10, 2016 at 7:59

Community · Accepted Answer · 2017-05-23 12:15:38Z

1

From this article stack overflow article: Oracle 11g - Check constraint with RegEx you can see that regex works on check constraints.

A regex which supports what you want would be:

^([0-9]+|N/A)$

^ at start of expression means start of line/row/text; [0-9]+ digits 1 to x; | or operator; N/A a specific text; $ end of line/row/text

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 10, 2016 at 8:15

Markus Zaczek

1546 bronze badges

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

2 Comments

smrita Over a year ago

Thanks ... But this regex is allowing me to insert other charaters as well :(

Markus Zaczek Over a year ago

I got a problem testing it right now. But i guess i missed the boundaries from where to where the string has to be found. I'll update my answer.

Rahul Tripathi · Accepted Answer · 2016-02-10 08:16:38Z

0

You can try like this:

 CHECK (column IN (REGEXP_LIKE(column, '^[[:digit:]]{9}$'), 'N/A')

answered Feb 10, 2016 at 8:16

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

Adding constraint in the column oracle

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related