I would like to be able to round a number to n significant figures in SQL. So:
123.456 rounded to 2sf would give 120
0.00123 rounded to 2sf would give 0.0012
I am aware of the ROUND() function, which rounds to n decimal places rather than significant figures.
5条答案
按热度按时间iyr7buue1#
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.
cotxawn72#
Adapted the most popular answer by Brann to MySQL for those who come looking like me.
/* Felt too long to put in comment */
ROUND(num, sf - 1 - FLOOR(LOG10(ABS(num))))
8yoxcaq73#
I think I've managed it.
dl5txlt94#
You could divide by 100 before rounding and then multiplying by 100...
whlutmcx5#
Using formatting:
Or for example to hardcode 3 significant digits: