number_format() with MySQL

user_230202 0
Viewed 136352

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?

mysql
8 Answers

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.

user_2323610

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), ".", "@"), ",", "."), "@", ",")
user_2334060

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'
user_1754220 
CREATE DEFINER=`yourfunctionname`@`%` FUNCTION `money`(
     `betrag` DECIMAL(10,2)

 )
 RETURNS varchar(128) CHARSET latin1
 LANGUAGE SQL
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT ''
 return(
     select replace(format(cast(betrag as char),2),',',"'") as betrag
 )

will creating a MySql-Function with this Code:

 select replace(format(cast(amount as char),2),',',"'") as amount_formated
user_18322220
Related