更新时间:2023-11-08 17:57:40
您可以尝试
SELECT AVG(CONVERT(
REPLACE(REPLACE(value,'。',''),',','。'),
DECIMAL(10,2)))
FROM`table`
WHERE`group` = 1
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