Format CPF column with regular expression

Question

In a table containing the column CPF I would like to format it in order to present the content properly. By appropriate way I refer to the format 999.999.999-99. It is possible to format this content through regular expression?

Sqlfiddle for the test

Answer 1

What you want is possible using the function regexp_replace:

SELECT REGEXP_REPLACE(cpf,
        '([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{2})',
        '\1.\2.\3-\4') AS cpf_formatado
FROM cadastro;

The regular expression will divide the digits into 3 groups of 3 and the end into a group of 2. Finally these groups are reordered with the divisions '\1.\2.\3-\4'.

Example in sqlfiddle.

Answer 2

It is not a good practice in database to store already formatted data. It is up to the programmer to format the data in the best way when displaying in the interface, the function of the database is only to provide this data, in a raw way.