Formatting / masking CPF in MySQL or PDO

1

I have a "cpf" field in my table. This field can be of type INT or VARCHAR, in case I need to change it to solve my problem. In it there are only numerical values, without the dashes and without hyphen (.-).

In PHP, when I'm going to call data via SELECT of MySQL or PDO, I need to format this value so that it shows me in the 000.000.000-00 format which is the default for CPF. "I need to do this in the query, so I do not have to go through the data again."

Does anyone know how to do this?

    
asked by anonymous 16.02.2016 / 22:58

2 answers

10

Using INSERT

To add characters to a result, you can use the INSERT function. Do not confuse with the syntax INSERT INTO , we are talking about the string function.

 SELECT INSERT( INSERT( INSERT( cpf, 10, 0, '-' ), 7, 0, '.' ), 4, 0, '.' )

Explaining:

  • The next two add the dots in the correct places.

Syntax:

INSERT(str,pos,len,newstr)
        │   │   │    └───── string a ser inserida
        │   │   └────────── quantos caracteres serão deletados na posição
        │   └────────────── posição onde a string será inserida
        └────────────────── campo desejado ou string original


PHP Equivalent:

substr_replace(substr_replace(substr_replace( $cpf, '-', 9, 0 ), '.', 6, 0 ), '.', 3, 0 );


Using INSERT and -

An alternative would be to use CONCAT to pick up each "piece" of the CPF, and paste the pieces and divisors using SUBSTR :

SELECT CONCAT(SUBSTR(cpf,1,3),'.',SUBSTR(cpf,4,3),'.',SUBSTR(cpf,7,3),'-',SUBSTR(cpf,10,2))

I think that SUBSTR is nicer in this case (at least a lot shorter to write, in addition to referencing the field in one place only).

In any case, the second example may be useful for other uses. It's not as noble as a kintsugi , but CONCAT has its moments of glory.


PHP Equivalent:

substr($cpf,0,3).'.'.substr($cpf,3,3).'.'.substr($cpf,6,3).'-'.substr($cpf,9,2);
    
16.02.2016 / 23:25
0

You can do this by creating a FUNCTION in your database and use it in your SELECT, as follows:

DELIMITER //

CREATE FUNCTION format_cpf(cpf VARCHAR(11))
RETURNS VARCHAR(14)
    BEGIN

        RETURN CONCAT( SUBSTRING(cpf,1,3) , '.',
            SUBSTRING(cpf,4,3), '.',
            SUBSTRING(cpf,7,3), '-',
            SUBSTRING(cpf,10,2));

END //

A table with some sample insert's:

CREATE TABLE pessoa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100),
    cpf VARCHAR(11)
);

INSERT INTO pessoa (nome, cpf) VALUES
('João', '12034578222'),
('Pedro', '12578963489'),
('Fernanda', '9657825410x');

The SELECT would look like this using FUNCTION format_cpf:

SELECT nome, format_cpf(cpf) cpf FROM pessoa;

Result:

OptionwithPHPusingRegularExpression:

<?php$cpf='00000000000';//000.000.000-00$pattern='/^([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{2})$/';$replacement='$1.$2.$3-$4';echopreg_replace($pattern,$replacement,$cpf);//000.000.000-00

Tip:YoushouldusetheCPFfieldonlyasvarcharinthedatabase,becauseonsomeoccasionsthereareCPF'sthatendwiththeletter"x".

    
16.02.2016 / 23:08