How to check if there are only numbers in the CPF and CEP in SQL

Question

I’m doing a database job and I need to check if the CPF and CEP have only numbers, since they are as varchar, does not need to be valid (for now), but if there are letters, not to be registered. Below follows the code

CREATE TABLE clientes (
cod_cliente SERIAL PRIMARY KEY,
nome VARCHAR (60) NOT NULL,
CPF VARCHAR (11) NOT NULL,
email VARCHAR(50),
telefone INTEGER,
data_cadastro DATE NOT NULL
);


CREATE TABLE endereco(
cod_end SERIAL PRIMARY KEY,
logradouro VARCHAR (50),
cep VARCHAR(8) NOT NULL,
cod_cidade INTEGER NOT NULL,
cod_cliente INTEGER NOT NULL 
);


CREATE TABLE estado(
cod_estado SERIAL PRIMARY KEY,
nome VARCHAR (60) NOT NULL,
uf VARCHAR (50) NOT NULL
);


CREATE TABLE cidade(
cod_cidade SERIAL PRIMARY KEY,
nome VARCHAR (60) NOT NULL,
cod_estado INTEGER NOT NULL
);





ALTER TABLE endereço ADD CONSTRAINT endereço_fk1 FOREIGN KEY (cod_cliente) REFERENCES clientes (cod_cliente);
ALTER TABLE endereço ADD CONSTRAINT estado_fk2 FOREIGN KEY (cod_cidade) REFERENCES cidade (cod_cidade);
ALTER TABLE cidade ADD CONSTRAINT cidade_fk1 FOREIGN KEY (cod_estado) REFERENCES estado (cod_estado);




ALTER TABLE clientes ADD CONSTRAINT tabela_ck CHECK (coluna1 > 0);


ALTER TABLE tabela ADD CONSTRAINT tabela_ck CHECK (coluna1 > 0);


ALTER TABLE tabela ADD CONSTRAINT tabela_ck CHECK (coluna1 > 0);


ALTER TABLE tabela ADD CONSTRAINT tabela_ck CHECK (coluna1 > 0);







INSERT INTO estado VALUES ('Rio Grande do Sul', 'RS');
INSERT INTO estado VALUES ('Santa Catarina', 'SC');
INSERT INTO estado VALUES ('Parana', 'PR');
INSERT INTO estado VALUES ('Mato Grosso', 'MT');
INSERT INTO estado VALUES ('Minas Gerais','MG');



INSERT INTO cidade VALUES ('Caxias do Sul',);
INSERT INTO cidade VALUES ('Florianópolis',);
INSERT INTO cidade VALUES ('Cabo Verde',);
INSERT INTO cidade VALUES ('Cascavel',);
INSERT INTO cidade VALUES ('Cuibá',);


INSERT INTO clientes VALUES ('Naiara', '03365282050', '[email protected]', '54991207030','2019/11/28');
INSERT INTO clientes VALUES ('Mateus', '03033611079', '[email protected]', '5491888899','2019/11/28');
INSERT INTO clientes VALUES ('Lucas', '75424991068', '[email protected], '5499224455');
INSERT INTO clientes VALUES ('Gabriel', '03006364074', '[email protected]', '5499433379','2019/11/28');
INSERT INTO clientes VALUES ('Janaina', '03268542783', '[email protected]', '48981542365','2019/11/28');
INSERT INTO clientes VALUES ('Marcos', '02565985210', '[email protected]', '54991234567','2019/11/28');
INSERT INTO clientes VALUES ('Joanna', '89652336587', '[email protected]', '54999876543','2019/11/28');
INSERT INTO clientes VALUES ('Juliana', '05263587412', '[email protected]', '54996548752','2019/11/28');
INSERT INTO clientes VALUES ('Alexandre', '96385274114', '[email protected]', '54984526314','2019/11/28');
INSERT INTO clientes VALUES ('Gustavo', '12345678998', '[email protected]', '54991105287','2019/11/28');


INSERT INTO endereco (logradouro, cep, cod_cidade, cod_cliente)
VALUES ('Rua Amabile Fontana', '95010000', , ), ('Avenida Castelo Branco', '95250000', , ), ('Rua Bela Vista', '69999000', , ), ('Travessa Antônio Carneiro Pinto', '90010020', , ), ('Rua 15 de Novembro'  '98995000', , );

Answer 1

This can be done within oneself input, create a:

<input type="number" name="cpf" />

It will send only numbers. What you have to be careful about is that this field can send negative numbers, however, you can do a validation in PHP with str_replace():

$cpf = $_POST['cpf'];

$replace = str_replace('-', '', $cpf);

Answer 2

If you are using Postgresql, which seems to me to be the case, you can use regular expressions:

teste=# SELECT regexp_replace('03365282050', '[[:digit:]]+', '', 'g') = '';
 ?column? 
----------
 t
(1 registro)

teste=# SELECT regexp_replace('03365abc050', '[[:digit:]]+', '', 'g') = '';
 ?column? 
----------
 f
(1 registro)

or, more simply:

teste=# SELECT '03365123050' ~ '[^[:digit:]]';
 ?column? 
----------
 f
(1 registro)

teste=# SELECT '03365abc050' ~ '[^[:digit:]]';
 ?column? 
----------
 t
(1 registro)