Question

我们需要一种途径,从我的栏目decimal (23,2) NOT NUL 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)

如何在神话中这样做? 用插手取代?

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

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

在Mysql 6.1中,你将有能力做成MAT(X,D[当地名称])

和

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

现在,这种能力并不存在,尽管您能够把我放在你的数据库中。检查结果。

Answer 3

你们可以使用

SELECT round(123.4566,2)  -> 123.46

Answer 4

FORMAT(X,D) 表格X以编号、编号编号编号、编号编号编号、编号编号##、编号编号编号编号编号、四舍五入到D decimal 地点等格式,并将结果作为示意图。如果D0,则结果没有点数或分数部分。

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

Answer 5

Antonio sponsa

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

错误;结果可能不正确!

例如,如果“数目”为12345.67,则由此引起的范围将是:

 12.346,67

而不是

 12.345,67

这是因为,如果单分部分大于或等于0.5(如我所举)的几轮“数字”!

你们的使用

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

如果您的MySQL/MDBarias FORMAT不支持“当地人——姓名”(见MindStalker的哨所――Thx 4, 棕榈)。注

Answer 6

至少可追溯到MySQL.5为止,你可以使用以下格式:

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

Answer 7

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
 )

• :

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

Answer 8

你们需要:

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

友情链接