MySQL: Convert String to Float/Decimal in german language

mysql string floating-point

15,773

You could try

SELECT AVG(CONVERT(
    REPLACE(REPLACE(value, '.', ''), ',', '.'), 
    DECIMAL(10,2))) 
FROM `table`
WHERE `group`=1

15,773

Christoph Mühlmann

I'm a research assistant at the Research Institute for Horticulture Weihenstephan, Germany, focusing on horticultural computer science. Different applications for horticultural businesses are created by a team of 3-4 developers. Most of us have a horticultural background combined with good computer-science skills. My computer-science background Coding with: C#/VB.Net (Windows, Windows CE), PHP (Webapps), Java (Android, platform-independent, Webapps). Databases: MySQL, PostgreSQL, SQLite. OS'es: Debian GNU/Linux (for ~15 years), Windows - if it's not preventable ;-) My horticultural background I'm a gardener. I learned vegetable production at a 55ha organic farm over two years (work + school). After that training, I continued to work there for some time. Later, I studied horticultural sciences, which resulted in an diploma given by an university of applied sciences.

Updated on June 04, 2022

Comments

Christoph Mühlmann almost 2 years
In a MySQL-table I have a VARCHAR-column with different values, which may represent String-, Integer-, Float-, Whatever-Values. These Values are written as a language-specific String into the Database, this means a float-value of 123.45 may be written as a String like "123,45" in german language (using VB.Net...)

As I need average values of float-values wich are in the same group: How can I cast such a string to a FLOAT within MySQL? Simply AVG(CONVERT(value, DECIMAL)) won't work (returns 99.00000), conversion to FLOAT is not possible.

Charset is utf8, Collation is utf8_general_ci.

Sample table:
```
id | value  | group
1  | 122,45 | 1
2  |  66,34 | 1
3  | blabla | 2
4  | 109,21 | 1
5  | bababa | 2
```
Goal: somethig like SELECT AVG(CONVERT(value, DECIMAL)) FROM table WHERE (group=1) should result in 99.333333, not 99.

Any Ideas?

Christoph

PS.: I did not make that database-layout...
Christoph Mühlmann over 12 years

this may fail in some situations: in german language a value of T1234.56` may be written "1.234,56". The above would result in the string "1.234.56" which would be converted to 1.
Christoph Mühlmann over 12 years

Your edited code may work and may not: what, if the point is a decimal-point? I decided to change the database layout a little, now I have a column for numeric data (float, int and boolean will be saved in a DECIMAL(30,10)-column) and another column for other data (text, serialized data, binary data, whatever). Works better and allowes to calculate with the values in the numeric column in MySQL directly (AVG/MIN/MAX f.e.)
Marco over 12 years

@ChristophMühlmann: I agree with you, but your example used comma as decimal separator!!
Christoph Mühlmann over 12 years

You are right with this. But I thought it would be better to change the database-layout in a way I don't need to differ between numeric and non-numeric values in one column anymore. The main problem was the database-layout, which was not quite well.
Marco over 12 years

@ChristophMühlmann: yes, you're right with this. You did the right thing :)