Formatting / masking CPF in MySQL or PDO

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);

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".