Detailed explanation of formatting numbers in MySQL

Recently, due to work needs, I need to format numbers in MySQL, but I found that there is little information on the Internet. I simply summarize it myself to help friends in need. Let's take a look at the detailed introduction together:

1. format function:

Format floating point number format(number, length);

Description: 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.

Sample Code

mysql> SELECT FORMAT(12332.123456, 4);
 -> '12,332.1235' 
mysql> SELECT FORMAT(12332.1,4);
 -> '12,332.1000' 
mysql> SELECT FORMAT(12332.2,0);
 -> '12,332'

2. rpad and lpad give a number of digits, but if it is insufficient, add custom characters

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.

Sample Code

mysql> SELECT RPAD('hi',5,'?'); 
  -> 'hi???'
 mysql> SELECT RPAD('hi',1,'?'); 
  -> 'h'
mysql>SELET RPAD(12, 5 ,0);
  ->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.

Sample Code

mysql> SELECT LPAD('hi',4,'??');
 -> '??hi' 
mysql> SELECT LPAD('hi',1,'??');
 -> 'h' 

mysql>SELECT LPAD(12, 5, 0)
  ->'00012'

Reference: http://www.cnblogs.com/fenglie/articles/4409208.html

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.