patternsqlModerate
Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server
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.
The output is:
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.
Which worked great by using a simple divide.
lets assume my DB consists of two columns. Sharename and Sharesize.
Sharename | SharesizeShare 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,685107549Now 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
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.