SQL server format numbers

Use SQL statements to format double-type data. For example, only two digits after the decimal point are retrieved.
I. Main method

-- Returns the number before the decimal place, excluding rounding.
Select left ('2014. 100', charindex ('.', '2014. 100')-1)
Where: charindex ('. ',' 30000. 72234 ') to get the decimal point.-1 indicates all the digits before the decimal point. If you want to get n after the decimal point, you can write it as + n. It is quite useful.

-- Each three digits are separated by commas (,), leaving two decimal places. Rounding is not considered.
Select convert (varchar, cast (round (30000.72234, 0) as money), 1)

-- Each three digits are separated by commas (,), leaving no decimal places. Rounding is considered.
Select left (convert (varchar, cast (round (30000.72234, 0) as money), 1), charindex ('. ', convert (varchar, cast (round (30000.72234, 0) as money), 1)-1)

II. Comparison between CAST and ROUND
1.
Select cast ('192. 100' as decimal) will get 123 (the decimal point will be omitted ).
If you want to get two digits after the decimal point.
You need to change the above
Select cast ('1970. 100' as decimal (38, 2 ))

==>> 123.46
Auto rounding!

2.
Select round (123.75633, 2, 1 ),
ROUND (123.75633, 2)
The two values obtained from the preceding SQL statement are different. The first one is 123.75000, and the last one is 123.76000.
Because the former is truncated after the decimal point before rounding, and retains two digits.
While the latter is not intercepted, it will naturally get 123.76000

Figure 1:

Figure 2:

The SQL statement is as follows:

Select convert (VARCHAR (15), CAST (CONVERT (DECIMAL (12,2), LTRIM (PREPAY) as money), 1) PREPAY_V, PREPAY FROM INVOICEHD

Go

Format: CONVERT (VARCHAR (15), CAST (CONVERT (DECIMAL (12, 2), LTRIM (database tutorial field name) as money), 1)

Note:

Select convert (VARCHAR (100), CAST (CONVERT (DECIMAL (111111111111111), LTRIM ('192. 00') as money), 1) money

Select convert (VARCHAR (100), CAST (CONVERT (DECIMAL (111111111111111), LTRIM ('192. 00') as money), 1) money