return of decimal places in sqlserver queries

Asked

Viewed 36,844 times

1

In the query below I would like to return only 3 houses after the comma for the columns: size in Mb, availablespaceinmb Already for column available, I would like to return without any decimal place after the comma.

How can I do that.

SELECT
rtrim(name)as name,
((size)/128.0) as'size in MB',
((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0 AS AvailableSpaceInMB,
((((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0)/((size)/128.0))
* 100 as'% Available', filename
FROM sysfiles
sql-server

3 answers

4


If you are talking about presentation of the result and use MS SQL Server 2012 or higher, you can use the function FORMAT to determine the number of boxes after the comma, thus:

select format(1234.5678, '#.000')

The result will be 1234.568 (see that it was rounded).

And, to present without any decimal place:

select format(1234.5678, '#')

The result will be 1235 (also rounded).

Update: In previous versions, you can use the function ROUND:

select round(1234.5678, 3)
select round(1234.5678, 0)

Results: 1234.568 and 1235.

Or you can convert the value to DECIMAL limiting the number of decimal places:

select cast(1234.5678 as decimal(10, 3))
select cast(1234.5678 as decimal(10, 0))

Results: also 1234.568 and 1235.

At your command, simply apply the chosen method to each column resulting from the SELECT command. For example:

SELECT
    rtrim(name)as name,
    ROUND(((size)/128.0), 3) as'size in MB',
    ROUND(((size)/128.0) -CAST(FILEPROPERTY(name,'SpaceUsed')AS int)/128.0, 3) 
            AS AvailableSpaceInMB,
    ROUND(((((size)/128.0) 
            -CAST(FILEPROPERTY(name,'SpaceUsed') AS int)/128.0)
            /((size)/128.0)) * 100, 0) as'% Available', 
    filename
FROM 
    sysfiles

  • Could you please give me an example for versions prior to 2012? Can you also format this query I left so that it is displayed in these conditions? Thanks.

  • @Jeanbraz I updated my reply with options for previous versions.

1

Right,

There are two ways:

1° Form:

First returning a number in varchar:

select cast(123789.456 as varchar))

Second returning where is the point to separate the number of decimals.

select charindex('.',cast(123789.456 as varchar))

Finally Returning the number of the third decimal place, considering the point index + the amount of excess numbers to the third decimal place

select SUBSTRING(cast(123789.456 as varchar), charindex('.',cast(123789.456 as varchar))+3,1)

2° Form: Returning the final number of decimals of a rounded number: -- Truncated the third house

select right(cast(round(123789.45678, 3, 1) as numeric(15,3)),1)

-- Rounding the third house

select right(cast(round(123789.45678, 3) as numeric(15,3)),1)

0

Hello, to bring the results with with decimal separator I did so.

DECLARE @VALOR  DECIMAL(15,2)
DECLARE @X  INT

SET @VALOR  = 2111113.55
SET @X = CASE   WHEN LEN(@VALOR) BETWEEN 1 AND 6 THEN 3
                WHEN LEN(@VALOR) BETWEEN 7 AND 9 THEN 2
                WHEN LEN(@VALOR) BETWEEN 10 AND 12 THEN 1 
                WHEN LEN(@VALOR) BETWEEN 13 AND 15 THEN 0 END

SELECT REPLACE(LEFT(convert(varchar,cast(@VALOR as money),1),(LEN(@VALOR)-@X)),',','.')  + ',' + RIGHT(@VALOR,2)

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.