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ólar | Reais |
| US$ 1,250.75 | R$ 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.
Obrigado, mas primeiro eu faço select * from tabela
Olá Loco Pai,
Obrigado pelo feedback.
Abraços.
Valeu. Serviu demais.
Obrigado Morais, sempre as ordens.
Abraços.
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
: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.
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) +';');
Muito bom! O autor deveria colocar como outra opção no post.
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?
Muito bom!!! Obrigado pela dica, resolveu um grande problema!
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.
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
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.
Este comentário foi removido pelo autor.
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
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.
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 |
As vezes eu crio umas funções para resolver problemas, não sabia sobre a format, criei uma função, de certa forma ainda continuo com ela afinal eu consigo colocar muitos caracteres no valor que quero de retorno apesar de parecer muito louca ela não buga nem nada, e é rapida
—
CREATE DEFINER=`root`@`%` FUNCTION `number_f`(num varchar(255)) RETURNS varchar(2555) CHARSET utf8
BEGIN
declare decimal_z,inteiro_z,response_z varchar(255);
declare cout_z,cout2_z,aux int;
set response_z = '';
set decimal_z =
if(CHAR_LENGTH(SUBSTRING_INDEX(num,'.',-1))=CHAR_LENGTH(num),
SUBSTRING_INDEX(num,',',-1),
SUBSTRING_INDEX(num,'.',-1)
);
set decimal_z =
if(CHAR_LENGTH(decimal_z)!=CHAR_LENGTH(num),
concat(',',decimal_z),
''
);
set inteiro_z = if(CHAR_LENGTH(SUBSTRING_INDEX(num,'.',1))=CHAR_LENGTH(num),
SUBSTRING_INDEX(num,',',1),
SUBSTRING_INDEX(num,'.',1)
);
set cout_z = CHAR_LENGTH(inteiro_z);
set cout2_z = 0;
WHILE cout_z > cout2_z DO
— SET response_z = CONCAT('cout_z=',cout_z,'; cout2_z=',cout2_z,' ',if(response_z = '','',concat('.',response_z)));
IF(cout_z-cout2_z < 3)THEN
SET aux = (cout2_z-cout_z);
SET cout2_z = cout2_z + (cout_z-cout2_z);
SET response_z = concat(SUBSTRING(inteiro_z,if((cout_z-cout2_z)<0,1,-cout2_z),-(aux)),(if(response_z = '','',concat('.',response_z))));
ELSE
set cout2_z = cout2_z + 3;
SET response_z = concat(SUBSTRING(inteiro_z,if((cout_z-cout2_z)<0,1,-cout2_z),if((cout_z-cout2_z)<0,((cout_z-cout2_z)-((cout_z-cout2_z)*2)-1),3)),(if(response_z = '','',concat('.',response_z))));
END IF;
END WHILE;
return CONCAT(
response_z,decimal_z
);
END
—
É possivel Formartar toda a Coluna do Acess em Moeda usando o VBA ?
Obrigado!