Concatenate CPF in Mysql

Asked

Viewed 416 times

1

Good Afternoon

I would like to know how to concatenate 11 digits in the format of a CPF. Like, 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;

Only I’m not getting 'take' this same idea and leave to format CPF. I’m already tapping pin here. Thanks

mysql
  • 1

    what is the difficulty/error? if Cpf has this format "123.456.789-00" only count the position of each group and change the substring

  • The difficulty was only to change this SQL that I informed to be able to serve for the CPF too. But already the staff already helped me. Thanks to you too.

2 answers

2

Would that be:

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

See here an example working: http://sqlfiddle.com/#! 9/9c7c0/2

  • Perfect... that’s exactly what I was struggling with.

  • Good, don’t forget to vote if the answer was helpful

  • Sure... Thank you again

2


Here is 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 informed UPDATE:

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.