Add check constraint regex to Oracle Database

Question

I have an application where a code like YYY/YY/YY or YYY.YY.YY is inserted before it was only with "."

So the previous developer add a constraint in the corresponding table that I drop in TEST environment but I want to add a new one

ALTER TABLE DEPLOYMENT_ADM.DEPLOYMENT 
  ADD CONSTRAINT deployment_chk_billing 
  CHECK (   BILLING_CODE LIKE '[0-9]{3}/[0-9]{2}/[0-9]{2}' 
         OR BILLING_CODE LIKE '[0-9]{3}\\.[0-9]{2}\\.[0-9]{2}');

But when I try it I have

ORA-02293: cannot validate (DEPLOYMENT_ADM.DEPLOYMENT_CHK_BILLING) - check constraint violated

So I don't see where is the error

LIKE does not support regular expressions in SQL

user330315
– user330315

2019-11-05 15:33:22 +00:00
Commented Nov 5, 2019 at 15:33 — user330315
– user330315, Commented Nov 5, 2019 at 15:33

Gordon Linoff · Accepted Answer · 2019-11-06 13:30:00Z

1

You want regexp_like():

ALTER TABLE DEPLOYMENT_ADM.DEPLOYMENT ADD CONSTRAINT deployment_chk_billing
 CHECK (regexp_like(BILLING_CODE, '^[0-9]{3}/[0-9]{2}/[0-9]{2}|[0-9]{3}\\.[0-9]{2}\\.[0-9]{2}$');

EDIT:

I wanted to point out the LIKE was not correct with wildcards. I think the regular expression you want is:

ALTER TABLE DEPLOYMENT_ADM.DEPLOYMENT 
  ADD CONSTRAINT deployment_chk_billing 
  CHECK ( REGEXP_LIKE(BILLING_CODE, '([0-9]{3}/[0-9]{2}/[0-9]{2})|([0-9]{3}[.][0-9]{2}[.][0-9]{2})' ) );

Or if you don't care if . and / are in the same value:

ALTER TABLE DEPLOYMENT_ADM.DEPLOYMENT 
  ADD CONSTRAINT deployment_chk_billing 
  CHECK ( REGEXP_LIKE(BILLING_CODE, '[0-9]{3}[./][0-9]{2}[./][0-9]{2})) );

edited Nov 6, 2019 at 13:30

answered Nov 5, 2019 at 15:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

Chrᴉz remembers Monica Over a year ago

(regexp_like(BILLING_CODE, '^[0-9]{3}/[0-9]{2}/[0-9]{2}|[0-9]{3}\\.[0-9]{2}\\.[0-9]{2}$'); 2 open brackets, one closing bracket

nicobin Over a year ago

I test it and have the same error, but I see that regexp_like statement are used for select, insert, update and delete...

Stilgar Over a year ago

How likely is it that existing data violates the constraint you are trying to add?

Littlefoot Over a year ago

Highly likely, @Stilgar, according to error the OP mentioned in their initial message.

nicobin Over a year ago

I drop the constraint to add a new one so I don't see how a constraint can be violated. Also all data in the column are like the regex expression.

Collectives™ on Stack Overflow

Add check constraint regex to Oracle Database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related