How can I restrict a field in a table so that only a combination of parentheses, numbers and hyphens-CodePudding

I have the following table:

create table telephones(
    client_id char(9) not null CHECK (cif_cliente SIMILAR TO '[0-9]{8}[A-Z]'),
    telephone_number char(14)
);

I want to restrict the telephone_number field so that I can only enter values such as (123)-45-67-89 or (666)-22-33-55 where there are always 3 numbers in brackets at the beginning followed by a hyphen, 2 numbers with another hyphen, 2 numbers with another hyphen and 2 numbers at the end.

I tried this but when I try to insert a correct number I can't:

create table telephones(
    client_id char(9) not null CHECK (cif_cliente SIMILAR TO '[0-9]{8}[A-Z]'),
    telephone_number char(14) CHECK telephone_number SIMILAR TO '([0-9]{3})-[0-9]{2}-[0-9]{2}-[0-9]{2}'),
);

insert into telephones values('12345678K', '(666)-22-33-55');

Any idea of how to fix this?

CodePudding user response：

As a personal preference I don't use SIMILAR TO defined by the SQL language.

I jump from the simplistic LIKE (useful for most cases) to a regexp pattern matching for well-formed patterns. I don't find good uses cases for SIMILAR TO, but that's just me.

You can do:

create table telephones (
  client_id char(9) not null CHECK (client_id ~ '[0-9]{8}[A-Z]'),
  telephone_number char(14) check (telephone_number ~
    '\(\d{3}\)\-\d{2}\-\d{2}\-\d{2}'
  )
);

insert into telephones (client_id, telephone_number) 
  values ('12345678A', '(122)-45-67-89'); -- succeeds

insert into telephones (client_id, telephone_number) 
  values ('12345678A', '122-45-67-89'); -- fails

insert into telephones (client_id, telephone_number) 
  values ('12345678A', '(122)-45-6-89'); -- fails

See running example at db<>fiddle.