SQL reserved two-bit decimal implementation method

Problem:

Customer requirements, with the amount of data related to print out to retain two decimal places, such as: 13.2/13.200 to display as 13.20;

Analysis:

First look at the data in the database definition, all decimal (12,2), directly through the database query results will also retain two decimal places, but after the code processing, it will ignore the last "0" bit.

Here are two solutions:

One, the code processing, detected as the Amount field, do special processing; second, modify the query SQL statement to circumvent this problem; The first method, whether by data type or field name, is not a good judge of the amount, so choose the second method.

There are three ways to retain two decimal places in SQL, similar to the 2nd and 3 methods:

1. Using the round () function, round returns a numeric value, rounded to the specified length or precision, using the example:

SELECT ROUND(123.9994,3)--123.9990SELECT ROUND(123.9995,3)--124.0000SELECT ROUND(748.584,-1)--750.000SELECT ROUND(748.586,-2)--700.000SELECT ROUND(748.586,-3)--Arithmetic overflow error when error converts expression to data type numericSELECT ROUND(748.586,-4)--0.000, if length is negative and is greater than the number of digits before the decimal point, then round will return 0SELECT ROUND(151.75,0,0)--152.00 RoundingSELECT ROUND(151.75,0,1)--151.00 truncation

2. Using the CONVERT () function, use the example:

SELECT CONVERT(DECIMAL(2),13.123) -- 13.12

3. Using the cast () function, use the example:

SELECT CAST(13.123 as DECIMAL(2))- - 13.12

The three methods (in fact two), or the difference between, the difference is that round () is only rounded, still retains the following number of digits is 0, but convert () and cast () will truncate the following bits, example:

SELECT CONVERT(DECIMAL( -,2),13.123)--13.12SELECT CAST(13.123  as DECIMAL( -,2))--13.12SELECT ROUND(13.123,2)--13.120

According to the requirements of the 2nd scenario here, but only such processing is not enough, the return of the float type, after the code processing, will still shed the last "0" bit.

The Final Solution is: when the database queries, the float type data is converted to a character type, and then the code processing is returned to the client.

The specific implementation is:

SELECT RTRIM(CONVERT(DECIMAL(2),13.123 ))

With the use of RTrim (), returns a character expression with trailing spaces removed. ：

RTRIM (character expression)

Example:

SELECT RTRIM('Hello')+'%'                    --hello%SELECT RTRIM('Hello')+'%'                       --hello%SELECT RTRIM('Hello' + "' + ' World')+'%'      --helloworld%SELECT RTRIM('Hello' + "' + ' World')+'%'      --Hello world%SELECT RTRIM('Hello' + ' ' + ' World')+'%'      --Hello world%

SQL reserved two-bit decimal implementation method