Home > T-SQL > Trim or Round a float value – SQL Math Function

Trim or Round a float value – SQL Math Function


ROUND() function is used to round or trim a numeric value with precision to a specified length.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

This function always returns a value. It could be 0 (zero) if there is an error in the argument or the rounded numeric value. It’s return type is decided by the argument value type with little change. Data Type mapping is given below.

Numeric Expression  data type = Output data type

Tinyint or smallint or int = int
bigint = bigint
Decimal(p,s) or numeric (p, s) = decimal(p, s)
Money or smallmoney =     money
float or real = float

The round operation can be done on any side of the numeric value such as left (whole number) or right (precision). If the length value is positive number then it will round the precision value or if the length value is negative number then it will round the whole number.

The function value is the optional argument and it is used to tell the function to truncate or to round the given value. If the function value is 0 then it is round or if it is positive integer then it will be truncated.

Example

–Simple round operation. It rounds 2 precision value of a numeric value.
select round(100.234, 2)
Output: 100.230
 
select round(100.235, 2)
Output: 100.240
 
–Round the whole number
select round(145.999, -1)
Output: 150.000
 
select round(145.999, -2)
Output: 100.000
 
–Truncating numbers
SELECT ROUND(150.75, 0, 1)
Output: 150.00
 
SELECT ROUND(150.75, 1, 1)
Output: 150.70
 
SELECT ROUND(150.75, 1, 0)
Output: 150.80
 

There are other math functions such as FLOOR and CEILING to round the number with fixed precision. But, Round function is used to round the given value to the user defined length for both left and right of a numeric value.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: