number_format() with MySQL

Question

hey i need a way to get a formated number from my column decimal(23,2) NOT NULL DEFAULT '0.00'

in php i could use this function number_format('1111.00', 2, ',', '.'); it would return 1.111,00 (in Germany we use , to define decimal numbers)

how would i do this in mysql? with string replaces?

Answer 1

With performance penalty and if you need todo it only in SQL you can use the FORMAT function and 3 REPLACE : After the format replace the . with another char for example @, then replace the , with a . and then the chararacter you choose by a , which lead you for your example to 1.111,00

SELECT REPLACE(REPLACE(REPLACE(FORMAT("1111.00", 2), ".", "@"), ",", "."), "@", ",")

Answer 2

FORMAT(X,D) Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

 SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'

Answer 3

http://blogs.mysql.com/peterg/2009/04/

In Mysql 6.1 you will be able to do FORMAT(X,D [,locale_name] )

As in

 SELECT format(1234567,2,’de_DE’);

For now this ability does not exist, though you MAY be able to set your locale in your database my.ini check it out.

Answer 4

You need this:

CONCAT(REPLACE(FORMAT(number,0),',','.'),',',SUBSTRING_INDEX(FORMAT(number,2),'.',-1))