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, Feb 10 '16 at 07:59

score 1 · Answer 1 · edited May 23 '17 at 12:15

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 '17 at 12:15

Community

1
1

answered Feb 10 '16 at 08:15

Markus Zaczek

154
6

Thanks ... But this regex is allowing me to insert other charaters as well :( – smrita Feb 10 '16 at 08:53
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. – Markus Zaczek Feb 10 '16 at 12:32

score 0 · Answer 2 · answered Feb 10 '16 at 08:16

0

You can try like this:

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

answered Feb 10 '16 at 08:16

Rahul Tripathi

168,305
31
280
331

Adding constraint in the column oracle

2 Answers2