mySQL FLOAT or DECIMAL returning 5,25 not 5.25

why a comma not a decimal?

[txbakers]

Granted mySQL is European, but isn't there a way to set locale for using decimals not commas?

I'm running the DB value through the FormatNumber VB function to fill out the decimals.

[sun818]

Is this what you want?

mysql> SELECT REPLACE('5,25', ',', '.');

I do not work with mySQL. I look on Google SE and Groups, but could not find a parameter or field property tweak that would permanently display in the format you want. Maybe it is a language or character set property? Someone who knows mySQL can answer better than I can.

[txbakers]

not really since this number: 1,200.50 is rendered as 1.200,50 in the European style. I need to retreive the numbers in my locale.

[ergophobe]

Is this what you mean (it's from the manual, chap 7.4.12, miscelaneous functions):

FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D decimals. If D is 0, the result will have no decimal point or fractional part.

mysql> select FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> select FORMAT(12332.1,4);
-> '12,332.1000'
mysql> select FORMAT(12332.2,0);
-> '12,332'

This doesn't seem to address the actual issue you have. The thing is, by default, MySQL doesn't format the thousands places with ., so I wonder whether the formating is done at the API level and that's where you need to look.

Tom

Tom

[txbakers]

I found out it was in the character set being used. by default it is Latin1, which uses the European style of currency formatting.

I wrote a simple work around - using VB script, I just catch the number as it comes in then translate it to the US system. No biggie.

[sun818]

I read you can create an my.cnf file inside your data directory configuring your "locale". By this I assume they mean default-character-set. Try setting it to usa7:

default-character-set=usa7