Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server

Question

I have a database which has me a value, a sharesize in bytes and a sharename. Which is pretty accurate but not useful for my enduser. Therefore I transformed the value to GB. Which worked great by using a simple divide.

lets assume my DB consists of two columns. Sharename and Sharesize.

Sharename | Sharesize

---

Share 1 |71685107549
Now I run this:
SELECT TOP (1000) 
 [Sharename]
 ,[ShareSize]
 ,(ShareSize / 1e+9) SharesizeGB
 FROM [mydb].[dbo].[myshares]

The output is:

ShareSize SharesizeGB
71685107549 71,685107549

Now I need to replace the "," with a "dot" and round the result to just have two digits after the dot.

Is that possible using only a select statement ?

For my example its not needed to divide by 1024.

• McNets: Decimal point depends on the computer regional settings, why you need to replace it?
• Aaron Bertrand: And to be clear what SSMS shows you on your machine might be different from what SSMS (or any application) shows a different user on their machine.
• RayofCommand: @McNets , I do live in germany and I am tranforming the data from a german source to an english system.

Answer 1

The query returns a float data type, which is a binary structure that has no comma or dot decimal separator. It is the rendering application that converts the value into a string format for display purposes, which can honor the client's regional settings if programmed to do so.

Although you could change the T-SQL to return a formatted string like the example below, be aware client sorting will use string instead of numeric rules. Note this example explicitly specifies culture en_US, which uses a dot decimal separator, to override the current session language setting. Also added ORDER BY for deterministic results with the TOP clause.

SELECT TOP (1000) 
 [Sharename]
 ,[ShareSize]
 ,FORMAT(ShareSize / 1e+9, '#,##0.000000000000', 'en-US') SharesizeGB
FROM [mydb].[dbo].[myshares]
ORDER BY (ShareSize / 1e+9) DESC;