Вокруг к n Значащим цифрам в SQL

Question

Вокруг к n Значащим цифрам в SQL

Я хотел бы смочь к раунду число к n значащим цифрам в SQL. Так:

123.456 rounded to 2sf would give 120
0.00123 rounded to 2sf would give 0.0012

Я знаю о РАУНДЕ () функция, которая округляется к n десятичным разрядам, а не значащим цифрам.

20

sql sql-server tsql rounding significant-digits

задан Jonathan Leffler 22 April 2011 в 22:05

3 ответа

You could divide by 100 before rounding and then multiplying by 100...

0

ответ дан 30 November 2019 в 01:08

I think I've managed it.

CREATE FUNCTION RoundSigFig(@Number float, @Figures int)
RETURNS float
AS
BEGIN

    DECLARE @Answer float;

    SET @Answer = (
    SELECT
        CASE WHEN intPower IS NULL THEN 0
        ELSE FLOOR(fltNumber * POWER(CAST(10 AS float), intPower) + 0.5) 
                * POWER(CAST(10 AS float), -intPower)
        END AS ans
    FROM (
        SELECT
            @Number AS fltNumber,
            CASE WHEN @Number > 0
                THEN -((CEILING(LOG10(@Number)) - @Figures))
            WHEN @Number < 0
                THEN -((FLOOR(LOG10(@Number)) - @Figures))
            ELSE NULL END AS intPower       
        ) t
    );

    RETURN @Answer;
END

0

ответ дан 30 November 2019 в 01:08

Другие вопросы по тегам:

sql sql-server tsql rounding significant-digits

Похожие вопросы:

score 20 · Accepted Answer

select round(@number,@sf-1- floor(log10(abs(@number)))) should do the trick !

Successfully tested on your two examples.

Edit : Calling this function on @number=0 won't work. You should add a test for this before using this code.

create function sfround(@number float, @sf int) returns float as
begin
    declare @r float
    select @r = case when @number = 0 then 0 else round(@number ,@sf -1-floor(log10(abs(@number )))) end
    return (@r)
end