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?
This question is related to
mysql
You need this:
CONCAT(REPLACE(FORMAT(number,0),',','.'),',',SUBSTRING_INDEX(FORMAT(number,2),'.',-1))
You can use
SELECT round(123.4566,2) -> 123.46
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
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'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.
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
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.
Source: Stackoverflow.com