CAST for monetary value stored in text in MySQL

Navigation
1

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

Notice the query:

Inquiry 

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

Result 

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 query by doing the conversion to decimal without losing the precision of the values?

    
asked by anonymous 11.10.2016 / 16:08

1 answer

2

I was able to resolve using Replace together with CAST

Inquiry 

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

Result 

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
    
11.10.2016 / 16:29
Configure Namespace and autoload PSR-0 How to validate a wmsAuthSign hash in php?