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.