HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dividetranformationstatementsqlserverreplaceonebytesroundand

Problem

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.

Solution

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;

Code Snippets

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

Context

StackExchange Database Administrators Q#274698, answer score: 11

Revisions (0)

No revisions yet.