SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Round Natural Numbers to the nearest Power of 10

By Hannan Kravitz,

The function requires 3 parameters:

  1. Natural Number
  2. Nearest Power of 10
  3. Direction

Simple Use Cases:

SELECT * FROM dbo.udf_RoundNearest (1264,10,0) /* The Direction 0 indicates the nearest Power of 10 round down. Result = 1260 */
SELECT * FROM dbo.udf_RoundNearest (126412,1000,0) /* Result 126000*/
SELECT * FROM dbo.udf_RoundNearest (126412,1000,1) /* Result 127000*/

Integrating the function with a table/view/derived table/CTE:

;with tmp (Value) as
  (select 1236 union all select 6584 union all select 9999)

    select t.*, fn.Number
    from tmp t
    cross apply dbo.udf_RoundNearest (Value,100,0) fn

/* Value Number
   1236 1200
   6584 6500
   9999 9900 */

Total article views: 204 | Views in the last 30 days: 5
 
Related Articles
FORUM

copy the select results into text file

copy the select results into text file

FORUM

SQL select statement results to XL file

SQL select statement results to XL file

FORUM

how do i supress cursor select set results

how do i supress cursor select set results

FORUM

Select statement of view stays 5 hours to return results......

Select statement of view stays 5 hours to return results......

FORUM

How to Group and batch select/cursor results in stored procedure

How to Groupand batch select/cursor results for e-mail

Tags
inline function    
round    
round nearest    
 
Contribute