Oracle: How to add constraint to only allow a particular set of numbers in a string column

Question

I have a string column, which stores numbers. How can I add a constraint to make sure that the number consists of only 6 or 7. Example 676666 But I do not want 565677

There is not enough information here. Are you talking about a column in a table? A variable in a PL/SQL program? Something else? — Boneist, Mar 01 '17 at 10:46
Do you mean you have a column and database where the string length should be 6 or 7 characters only? — Utsav, Mar 01 '17 at 10:47
http://stackoverflow.com/questions/2441427/restrict-varchar-column-to-specific-values — Tom J Muthirenthi, Mar 01 '17 at 10:48
Oracle SQL, a column in table. it can be like 676666 but not 565677 — nostress, Mar 01 '17 at 10:48

score 4 · Accepted Answer · 2017-03-01T11:06:29.947

4

Add a check constraint:

CONSTRAINT check_numbers CHECK (regexp_like(the_column, '^[67]+$'))

This requires at last one digit.

edited Mar 01 '17 at 11:06

answered Mar 01 '17 at 11:02

It doesn't seem to be working for me. It does not accept this expression. – nostress Mar 01 '17 at 12:41
@nostress: what do you mean with "does not accept"? Works for me: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c7229678984cd47b957c783379c8a654 – Mar 01 '17 at 12:44
It doesnt accept this expression. it accepts '[67]' but not '^[67]+$' – nostress Mar 01 '17 at 12:56
Thank you, it was my mistake. I really appreciate your help! – nostress Mar 01 '17 at 12:58

score 1 · Answer 2 · answered Mar 01 '17 at 10:57

1

Add this constraint. I didn't run it but please try

CONSTRAINT CHECK_6_7_ONLY CHECK (regexp_replace(trim(column_name),'6|7','') is null)

Edit: Added trim as we are dealing with strings

answered Mar 01 '17 at 10:57

Utsav

7,914
2
17
38

score 1 · Answer 3 · answered Mar 01 '17 at 11:09

Another option is to use translate:

create table t42 (
  foo varchar2(10),
  constraint sixesorsevens check (translate(foo, 'x67', 'x') is null)
);

Table T42 created.

insert into t42 (foo) values (null);

1 row inserted.

SQL> insert into t42 (foo) values ('676666');

1 row inserted.

SQL> insert into t42 (foo) values ('576666');

insert into t42 (foo) values ('576666')
*
ERROR at line 1:
ORA-02290: check constraint (MYSCHEMA.SIXESORSEVENS) violated

... but the regexp_like() version might be clearer to someone who has to maintain this.

Oracle: How to add constraint to only allow a particular set of numbers in a string column

3 Answers3