3

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.

  • 5
    Decimal point depends on the computer regional settings, why you need to replace it? – McNets Aug 31 at 11:55
  • 1
    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. – Aaron Bertrand Aug 31 at 16:55
  • @McNets , I do live in germany and I am tranforming the data from a german source to an english system. – RayofCommand Sep 2 at 7:04
11

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.