mysql字符串转2位小数_MySQL：将字符串转换为德文的浮点/小数

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...

解决方案

You could try

SELECT AVG(CONVERT(

REPLACE(REPLACE(value, '.', ''), ',', '.'),

DECIMAL(10,2)))

FROM `table`

WHERE `group`=1