Concatenate CPF in Mysql

Question

Concatenate CPF in Mysql

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

Good Afternoon

I would like to know how to concatenate 11 digits in the form of a CPF. Type, format for a CNPJ, I did this:

update ger_ter set cpfcgc = CONCAT(SUBSTRING(cpfcgc, 1,2), '.', SUBSTRING(cpfcgc,3,3), '.', SUBSTRING(cpfcgc,6,3), '/', SUBSTRING(cpfcgc,9,4), '-', SUBSTRING(cpfcgc,13, 2))
where length(cpfcgc) =14 and pessoa=1;

I just can not 'get' this same idea and leave it to CPF format. I'm already tapping here. Thanks.

mysql

asked by anonymous 30.04.2018 / 20:00

2 answers

1

It would look like this:

CONCAT(SUBSTRING(cpfcgc, 1,3), '.', SUBSTRING(cpfcgc,4,3), '.', SUBSTRING(cpfcgc,7,3), '-', SUBSTRING(cpfcgc,10,2))

Here's a working example: link

30.04.2018 / 20:34

NullReferenceException in Winforms code Sum on a Pivot Table

score 1 · Accepted Answer

Here's a solution to apply the mask depending on the number of characters:

Example in a select:

SELECT
    cpfcgc,
    IF(
        LENGTH(cpfcgc) = 14,
         CONCAT(SUBSTRING(cpfcgc, 1,2), '.', SUBSTRING(cpfcgc,3,3), '.', SUBSTRING(cpfcgc,6,3), '/', SUBSTRING(cpfcgc,9,4), '-', SUBSTRING(cpfcgc,13, 2)),
        CONCAT(SUBSTRING(cpfcgc, 1,3), '.', SUBSTRING(cpfcgc,4,3), '.', SUBSTRING(cpfcgc,7,3), '-', SUBSTRING(cpfcgc,10,2))
    ) AS mascarado
FROM
    (SELECT '12345678909' AS cpfcgc UNION ALL SELECT '12345678000123' AS cpfcgc) AS X

Example applied to UPDATE reported:

UPDATE ger_ter
SET
    cpfcgc = IF(
        LENGTH(cpfcgc) = 14,
         CONCAT(SUBSTRING(cpfcgc, 1,2), '.', SUBSTRING(cpfcgc,3,3), '.', SUBSTRING(cpfcgc,6,3), '/', SUBSTRING(cpfcgc,9,4), '-', SUBSTRING(cpfcgc,13, 2)),
        CONCAT(SUBSTRING(cpfcgc, 1,3), '.', SUBSTRING(cpfcgc,4,3), '.', SUBSTRING(cpfcgc,7,3), '-', SUBSTRING(cpfcgc,10,2))
    );