Format/mask CPF in Mysql or PDO

Question

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

In PHP, when I will call the data via SELECT Mysql or PDO, I need to format this value to show me in the format 000.000.000-00 which is the default for CPF. "I need to do this in the query, so I don’t have to go through the data again".

Someone knows how to do it?

Answer 1

Using the function INSERT

To add characters to a result, you can use the function INSERT. Not to be confused with the syntax INSERT INTO, we are talking about the function of string.

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

Explaining:

• the INSERT more internal adds the - at the tenth position, and removes 0 characters (the second numeric parameter serves for substitution instead of insertion only)

• the following two add the points 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

Equivalent in PHP:

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

Using CONCAT and SUBSTR

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

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

I think the INSERT is nicer in this case (at least much shorter to write, besides referencing the field in one place).

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

Equivalent in PHP:

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

Answer 2

You can do this by creating a FUNCTION in your database and using 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');

SELECT would look like this with the use of FUNCTION format_cpf:

SELECT nome, format_cpf(cpf) cpf FROM pessoa;

Upshot:

Option with PHP using Regular Expression:

<?php
$cpf = '00000000000';//000.000.000-00

$pattern = '/^([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{2})$/';
$replacement = '$1.$2.$3-$4';
echo preg_replace($pattern, $replacement, $cpf);//000.000.000-00

Tip: You should use the CPF field only as varchar in the database, because on certain occasions there are CPF’s that end with the letter "x".