Detailed description of formatting numbers in mysql and detailed description of formatting in mysql

Detailed description of formatting numbers in mysql and detailed description of formatting in mysql

Recently, due to work requirements, you need to format the numbers in mysql. However, if you find that there is less information on the Internet, simply summarize it so that you can help friends who need it, let's take a look at the details below:

I. format functions:

Format floating point numberformat(number, length);

Introduction: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 shoshould 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. Given digits of rpad and lpad, insufficient to 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

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.