查看: 1546|回复: 0

[Mysql数据库] mysql中格式化数字详解

发表于 2017-10-9 08:00:02
句号论坛

最近因为工作的需求,需要对mysql中数字进行格式化,但发现网上的资料较少,索性自己总结一下,方便自己也帮助有需要的朋友们,下面话不多说,来一起看看详细的介绍:

一、format函数:

格式化浮点数 format(number, length);

介绍: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. D should be a constant value.

示例代码

  1. mysql> SELECT FORMAT(12332.123456, 4);
  2. -> '12,332.1235'
  3. mysql> SELECT FORMAT(12332.1,4);
  4. -> '12,332.1000'
  5. mysql> SELECT FORMAT(12332.2,0);
  6. -> '12,332'
复制代码

二、rpad 和 lpad 给定位数,不足补充自定义字符

RPAD:

Returns the string str,right-padded with the string padstr to a length of len characters. If
str is longer than len, the return value is shortened to len characters.

示例代码

  1. mysql> SELECT RPAD('hi',5,'?');
  2. -> 'hi???'
  3. mysql> SELECT RPAD('hi',1,'?');
  4. -> 'h'
  5. mysql>SELET RPAD(12, 5 ,0);
  6. ->12000
复制代码

This function is multi-byte safe.

LPAD:

Returns the string str, left-padded with the string padstr to a length of lencharacters. If str is longer than len, the return value is shortened to lencharacters.

示例代码

  1. mysql> SELECT LPAD('hi',4,'??');
  2. -> '??hi'
  3. mysql> SELECT LPAD('hi',1,'??');
  4. -> 'h'
  5. mysql>SELECT LPAD(12, 5 , 0)
  6. ->'00012'
复制代码

参考:http://www.cnblogs.com/fenglie/articles/4409208.html

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对程序员之家的支持。



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条