How to convert a 'pre-formatted' value to a float

Question

i have a my sql view (MY_VIEW) with value pre-formatted with

FORMAT(myValue,2,'DE_de') as Value

So, i have some values like: 123,18 ... 10.020,98 etc.

But, when i do another query based on MY_VIEW with a Sum(Value) , it return wrong value, because i suppose, it can't do SUM on a formatted value.

So i need to re-Cast Value to a float number but using CAST but it don't work:

SUM( CAST(Value AS Decimal(10,2) ))

Answer 1

I hope this might help you.

1. calling REPLACE() 2 times

mysql> SELECT CAST(REPLACE(REPLACE('1.123,45', ",", ""), ".", "") AS DECIMAL(10, 2)) AS converted;
+-----------+
| converted |
+-----------+
| 112345.00 |
+-----------+

2. using UDF(User Defined Function)

According to How to do a regular expression replace in MySQL?, you could you PREG_REPLACE() to replace "," or "." to "" at once using Regular expression after https://github.com/hholzgra/mysql-udf-regexp is installed into you MySQL Server