CAST for monetary value stored in text in Mysql

Asked

Viewed 281 times

1

In the table of products of the bank there is a field valor that has the type varchar(255)

Observe the query :

Consultation

SELECT
valor                        as valor_original,
CAST(valor AS DECIMAL(18,2)) as valor_convertido
from
produtos

Upshot 

valor_original | valor_convertido

170,00           170.00
204,80           204.00
447,95           447.00
170,00           170.00
209,00           209.00
230,40           230.00
139,00           139.00
209,00           209.00
315,00           315.00
230,40           230.00
170,00           170.00

How can I perform a consultation by performing the conversion in decimal without losing the accuracy of the values ?

mysql type-conversion

  • you wish to make a where with the valor_convertido?

  • 1

    Not a simple select even with it converted. I managed to resolve @Virgilionovic thank you for your attention !

1 answer

2


I managed to solve using the Replace together with CAST

Consultation 

SELECT
valor                          AS valor_original,
CAST(REPLACE(valor, ',', '.')  AS DECIMAL(18,2)) as valor_convertido
from
produtos

Upshot

valor_original | valor_convertido
170,00           170.00
204,80           204.80
447,95           447.95
170,00           170.00
209,00           209.00
230,40           230.40
139,00           139.00
209,00           209.00
315,00           315.00
230,40           230.40
170,00           170.00
  • 1

    i think only with a replace can problems if the values pass to thousand then I would: CAST(REPLACE(REPLACE(valor, '.', ''),',','.') AS DECIMAL(18,2)) as valor_convertido would avoid the problem.

Browser other questions tagged mysql type-conversion

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