number_format() with MySQL

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 d开发者_如何转开发efine decimal numbers)

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

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.

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

You can use

SELECT round(123.4566,2)  -> 123.46

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'

Antonio's answer

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

is wrong; it may produce incorrect results!

For example, if "number" is 12345.67, the resulting string would be:

'12.346,67'

instead of

'12.345,67'

because FORMAT(number,0) rounds "number" up if fractional part is greater or equal than 0.5 (as it is in my example)!

What you COULD use is

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

if your MySQL/MariaDB's FORMAT doesn't support "locale_name" (see MindStalker's post - Thx 4 that, pal). Note the FLOOR function I've added.

At least as far back as MySQL 5.5 you can use format:

SELECT FORMAT(123456789.123456789,2);
/* produces 123,456,789.12 */

SELECT FORMAT(123456789.123456789,2,'de_DE');
/* 
    produces 123.456.789,12
    note the swapped . and , for the de_DE locale (German-Germany) 
*/

From the MySQL docs: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format

Available locales are listed elsewhere in the docs: https://dev.mysql.com/doc/refman/5.5/en/locale-support.html

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

You need this:

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