CODEWITHSUNDEEP
sqldb2check-constraints
Bermie
Bermie

Reputation: 21

SQL DB2 CHECK-CONSTRAINT PATTERN

I'm trying to write a constraint pattern which only allows the following: a string as follows:

I tried a lot of ways but none of it worked the way i hoped and ended up doing something like this:

ALTER TABLE KUNDENKONTAKTDATEN
ADD CONSTRAINT 

VariableA_Check CHECK (
VariableA Like '+55 1[2-4][0-9] [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'    
Or VariableA Like '+55 1[2-4][0-9] [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'   
Or VariableA Like '+55 1[2-4][0-9] [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
Or VariableA Like '+55 150 [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
Or VariableA Like '+55 150 [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'    
Or VariableA Like '+55 150 [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Not really pretty but hoped it would work. Any suggestions? Thank you!

Upvotes: 1

Views: 639

Answers (1)

mao
mao

Reputation: 12267

If you are running Db2 Version 11.1 on Linux/Unix Windows, then try something like this:

create table tester (
 mycol varchar(50) constraint bad_value check  (regexp_like(mycol, '^\+55 (12[0-9]|13[0-9]|14[0-9]|150) [0-9]{8,10}$'))
);

You can use this website to test out and explain the regular expression.

Upvotes: 1

Related Questions