having difficulty with creating constraint in sqlite-CodePudding

I have a table with contact as one of the column with constraint following the pattern of (000)0000000000

create table patient
(USERNUM INT PRIMARY KEY,
CONTACT varchar(15) constraint check_contact check (contact like '%([0-9]*3)[0-9]*10%'),
age int constraint check_age check (age >0 and age <=100),
location varchar(50)
);

This does not allow me to insert the record though the pattern of number is same. I have altered my table with below command as an alternative

alter table patient add contact varchar(15) constraint ch_contact 
check (SUBSTRING(contact, 1, 1) = '(' and SUBSTRING(contact, 5, 1) = ')' 
and SUBSTRING(contact, 2, 4) like '[0-9]' and SUBSTRING(contact, 6, 15) like '[0-9]');

but it does not allow me to insert the record as below

insert into patient (USERNUM, CONTACT, AGE, LOCATION)
VALUES (5, '(000)0234567890', 34, 'VIZAG');
'''

CodePudding user response：

You might be able to use the GLOB operator here:

CONTACT varchar(15) CONSTRAINT check_contact
    CHECK (contact GLOB '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')