quarta-feira, 7 de dezembro de 2011

Função para Formatar Moeda em reais no MySQL

No MySQL os dados do tipo moeda são armazenados com ponto no separador de decimais, isto quando utilizamos os tipos de dados Decimal ou Numeric (Caso tenha dúvidas sobre o tipo de dados a utilizar, veja esta outra matéria).
O uso do ponto ocorre pois este é o padrão americano, vejamos um exemplo:


DólarReais
US$ 1,250.75R$ 1.250,75


Vejam que invertem-se os separadores.

Nota: No valor armazenado, a vírgula não existe, somente no retorno de uma instrução select.

Agora vejamos na prática como o banco de dados se comporta:
 Select valor From preco;  
 Results:  
 1250.75  

Atualizado em: 15/08/2015 - Sugestão do colega Maison Sakamoto

Para demonstrar os valores no formato apresentado no primeiro exemplo, podemos utilizar de dois formatos, vejamos:

Através da função nativa do MySQL
 Select format(valor,2,'de_DE') From preco;
Results:  
 1.250,75    

No format, fazemos usado do locale, passando como valor o Alemão.

A outra forma, desta manual, é com o uso de três funções do MySQL, são elas:

Concat
Replace
Format

Vejamos como resolvemos a situação:
 Select Concat('R$ ',   
               Replace  
                 (Replace  
                   (Replace  
                     (Format(1250.75, 2), '.', '|'), ',', '.'), '|', ','));  

Resolvido nosso problema, agora vamos ver o que fizemos:

1º - Utilizamos o concat para demonstrar o cifrão (R$) junto ao valor.
2º - Utilizamos o Replace para trocar o separador decimal ponto e setar o pipe (|) temporariamente.
3º - Utilizamos o Replace para trocar o separador vírgula por ponto.
4º - Utilizamos o Replace para trocar o separador setado temporariamente pipe por vírgula.
5º - Utilizamos o Format para dar um formato ao valor desejado.

Ficou complicado? Descomplicando então:

1º - A ação mais importante da rotina está na função Format que converte o valor para o formato americano, vejamos:
 Select Format(1250.75,2);  
 Results  
 1,250.75  

Com o format aplicado, podemos então provar o porque do uso do replace.

2º - Da direita para esquerda vejam que temos o ponto como separador de decimal, iremos substituí-lo pelo pipe.
 Replace('.','|')  

Mas ai você me pergunta, porque pipe?

Fácil, por que caso eu substitua direto por vírgula, o passo três não irá funcionar corretamente, que é a troca de vírgula por ponto, ai teríamos um retorno de:
 1.250.75   

O que não seria o que desejamos.
O pipe foi somente um exemplo, você pode colocar o que desejar, ponto e vírgula, mais, menos, seu nome e etc.

3º - Neste passo iremos retirar a vírgula que esta no separador de milhar, colocando em seu lugar o ponto:
 Replace(',','.')  

4º - Agora iremos trocar o pipe por ponto, deixando-o como separador de milhar.
 Replace('|','.')  

5º - Feito, nos resta agora somente concatenar o valor formatado com o cifrão:
 Concat('R$ ', ...)

Sem sombra de dúvidas o primeiro exemplo é muito mais prático, de qualquer forma, ambos apresentaram o mesmo resultado, ai fica a seu critério qual usar.

Espero mais uma vez ter lhes sido útil, um grande abraços.

Clique aqui e veja todas as matérias sobre MySQL

17 comentários:

  1. Obrigado, mas primeiro eu faço select * from tabela

    ResponderExcluir
    Respostas
    1. Olá Loco Pai,

      Obrigado pelo feedback.

      Abraços.

      Excluir
  2. Respostas
    1. Obrigado Morais, sempre as ordens.

      Abraços.

      Excluir
  3. Cara faz gambiarra não, usa o que já existe no mysql:

    Select Format(1250.75,2,'de_DE');
    resultado: 1.250,75


    Select Format(1250.075,3,'de_DE');
    resultado: 1.250,075

    ResponderExcluir
    Respostas
    1. :d

      Quem nunca usou um pouco de XGH que atire a primeira pedra.

      Sem sombra de dúvida o uso do locale e format é bem mais simples, por mais que demande um pouco de conhecimento, de qualquer forma, muito obrigado pela contribuição Maison.

      Grande abraço.

      Excluir
    2. Muito bom! O autor deveria colocar como outra opção no post.

      Excluir
  4. ZSQLUpdate.Script.Add('update venda set desconto_porcentagem = ROUND(desconto * 100 / sub_total, 2) + ',' +
    ' acrescimo_porcentagem = ROUND(acrescimo * 100 / sub_total, 2) '+
    ' where venda.idvenda = ' + QuotedStr(CbContrato.Text) +
    ' and venda.idfilial = ' + QuotedStr(vLojaLogadaID) +';');

    ResponderExcluir
  5. Existe alguma variável global no MySQL que gerencie a localidade do sistema monetário (no caso Alemanha) e que pode ser alterada para ficar sempre tratando o valor no formato alemão?

    ResponderExcluir
    Respostas
    1. Olá Yuri.

      O MySQL trabalha com variáveis de ambiente para datas, para formato monetário eu não conheço. Procurei por algo mas não achei, no entanto, não posso te garantir que não exista algo pra isto.

      Agradeço o feedback e, boa sorte na procura ai.

      Excluir
  6. Muito bom!!! Obrigado pela dica, resolveu um grande problema!

    ResponderExcluir
  7. Tenho o select abaixo, como faço para converter os valores ?

    SELECT
    cat.id,
    cat.nome,
    SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) AS qtd_tipo_0,
    SUM( IF( mov.tipo = 0, mov.total, 0 ) ) AS total_tipo_0,
    SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) AS qtd_tipo_1,
    SUM( IF( mov.tipo = 1, mov.total, 0 ) ) AS total_tipo_1,
    SUM( IF( mov.tipo = 2, mov.qtd, 0 ) ) AS qtd_tipo_2,
    SUM( IF( mov.tipo = 2, mov.total, 0 ) ) AS total_tipo_2,
    SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) - SUM( IF( mov.tipo = 2, mov.qtd, 0 ) ) - SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) as Saldo,
    SUM( IF( mov.tipo = 1, mov.total, 0 ) ) / SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) as medio,
    SUM( IF( mov.tipo = 0, mov.total, 0 ) ) / SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) as medio2

    FROM lc_movimento AS mov
    INNER JOIN lc_cat AS cat
    ON cat.id = mov.cat
    GROUP BY cat.nome ASC

    ResponderExcluir
    Respostas
    1. Olá Roberto.

      Adicione o format, conforme dica do colega Maison.

      format(sum( if( mov.tipo = 0, mov.qtd, 0 ) ),2,'de_DE') as qtd_tipo_0,
      format(sum( if( mov.tipo = 0, mov.total, 0 ) ),2,'de_DE') as total_tipo_0,
      format(sum( if( mov.tipo = 1, mov.qtd, 0 ) ),2,'de_DE') as qtd_tipo_1,
      format(sum( if( mov.tipo = 1, mov.total, 0 ) ),2,'de_DE') as total_tipo_1,
      format(sum( if( mov.tipo = 2, mov.qtd, 0 ) ),2,'de_DE') as qtd_tipo_2,
      format(sum( if( mov.tipo = 2, mov.total, 0 ) ),2,'de_DE') as total_tipo_2,
      format(sum( if( mov.tipo = 1, mov.qtd, 0 ) ) - sum( if( mov.tipo = 2, mov.qtd, 0 ) ) - sum( if( mov.tipo = 0, mov.qtd, 0 ) ),2,'de_DE') as Saldo,
      format(sum( if( mov.tipo = 1, mov.total, 0 ) ) / sum( if( mov.tipo = 1, mov.qtd, 0 ) ),2,'de_DE') as medio,
      format(sum( if( mov.tipo = 0, mov.total, 0 ) ) / sum( if( mov.tipo = 0, mov.qtd, 0 ) ),2,'de_DE') as medio2

      Avisa ai sobre o resultado. Abraços.

      Excluir
    2. Este comentário foi removido pelo autor.

      Excluir
    3. eu tinha tentando colocar mas da erro:

      #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'de_DE' )

      Se eu tirar 'de_DE' ele até funciona fica com apenas duas casas decimais mas não troca o ponto pela virgula

      Excluir
    4. Roberto, pode ser que a versão do seu banco não tenha suporte a função, tente aplicar a o select que mostrei acima, com o uso do replace.

      Excluir
  8. Fabiano, adorei o blog, cheguei aqui pesquisando pelo melhor uso de reais (float, numeric) e adorei o conteúdo.

    segue uma função pra quem quer retornar a string em reais de um numeric

    ***************************************
    CREATE DEFINER=`root`@`localhost` FUNCTION `f_reais`(`p1_valor` numeric(7,2)) RETURNS varchar(40) CHARSET utf8
    BEGIN
    DECLARE v1_retorno VARCHAR(40);

    Select Concat('R$ ',
    Replace
    (Replace
    (Replace
    (Format(p1_valor, 2), '.', '!'),
    ',',
    '.'),
    '!',
    ',')) INTO v1_retorno;

    RETURN v1_retorno;
    END
    ***************************************

    simplesmente use "select valor, f_reais(valor) as dimdim from tabela;"
    então retorna
    ______________________
    | valor | dimdim |
    ----------------------
    | 137.28 | R$ 137,28 |

    ResponderExcluir