Share number formatting examples in SQL

Use sql statements to format double data, for example, only take out the two digits after the decimal point
. Main method

--Take the number before the decimal place, regardless of rounding
select left('30000.72234', charindex('.', '30000.72234')-1)
Among them: charindex(' .', '30000.72234') to get the position of the decimal point; -1 means everything before the decimal point. If you want to get n after the decimal point, just write +n. It's quite useful.

--Separate every three digits with commas, leave 2 decimal places, and do not consider rounding
select convert(varchar, cast(round(30000.72234,0) as money), 1)

--Separate every three digits with commas, leave no decimal places, consider rounding
select left(convert(varchar, cast(round(30000.72234,0) as money), 1) , charindex('.', convert( varchar, cast(round(30000.72234,0) as money), 1))-1)


二. Comparison of CAST and ROUND
1.
SELECT CAST('123.456' as decimal) will get 123 (the decimal point will be omitted).
If you want to get the two decimal places.
You need to change the above to
SELECT CAST('123.456' as decimal(38, 2))

===>123.46
It will be automatically rounded!

2.
SELECT ROUND(123.75633, 2, 1),
ROUND(123.75633, 2)
The two values ​​obtained by the above SQL are different. The former one is: 123.75000, the latter one is: 123.76000.
Because the former has been truncated before rounding, leaving 2 digits after the decimal point.
The latter is not intercepted, and when rounded, it will naturally get 123.76000


Problem: It is necessary to calculate the ratio of the value queried in the Oracle database. SQL similar to the following:
Select Round((discount/Amount),2) from dual;

The numbers calculated in this way can meet most needs. But for numbers less than 1, a problem arises...data similar to .45 is displayed. The 0 in front of 0.45 is gone.

The following summarizes two methods to solve the problem of missing 0.

The first solution:
SELECT DECODE (TRUNC (1/100),0, REPLACE (1/100, '.', '0.'),TO_CHAR (1/100))
FROM DUAL;

You only need to replace 1/100 with the percentage you need.

The second solution:
SELECT RTrim(To_Char(1/100,'FM99999999990.9999'),'.') FROM dual;

The replacement method is the same as above.

Detailed comparison of the two methods:

The first formula is more complicated, but it can handle general numerical values.

The second one looks simple, but the number of digits in the formatted value must be greater than the number of digits in the calculation. For example, SELECT RTrim(To_Char(200,'FM90.9999'),'.') FROM dual; The displayed value is

##, because it has exceeded the formatting range.

--------------------

I found out that the value of field a in table tb is 0.4286, what should I do? It can be converted to a percentage of 42.9% (keep one decimal place after the decimal point)
For example
select a from tb
0.4286
The result I want to get is
42.9%

SQL code

select cast(cast(a*100 as decimal(18,1)) as varchar) + '%' from tb

#####

The above is the detailed content of Share number formatting examples in SQL. For more information, please follow other related articles on the PHP Chinese website!