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?
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), ".", "@"), ",", "."), "@", ",") 
format function that you can set (e,g, de_DE for german)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'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
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.
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)) 
