In
this article,We will see how to use the Arithmetic Functions in the SQL Server.
1. The Sign of a
Number:-
In
arithmetic, a number is considered as being negative (less than 0), null (equal
to 0), or positive (higher than 0). When a number is negative, it must have a -
symbol to its left. If it is positive, it may display a + symbol to its left or
it can omit it. A number without the - or + symbol to its left is considered
positive, also referred to as unsigned. The symbol that determines whether a
number is positive or negative is referred to as its sign. The sign is easily
verifiable if you know the number already. In some cases, when a number is
submitted to your application, before taking any action, you may need to get
this piece of information.
To
find out if a value is positive, null, or negative, Transact-SQL provides the
SIGN() function.
Syntax :-
SIGN(Expression)
This
function takes as argument a number or an expression that can be evaluated to a
number. The interpreter would then examine the number:
1. If the Expression is positive, the function
returns 1.
Example:-
DECLARE @Number As int;
SET @Number = 24.75;
SELECT SIGN(@Number) AS [Sign of 1058];
GO
Output:-
2.
If the Expression is null, the function returns 0
Example:-
DECLARE @Number As int;
SET @Number = 0;
SELECT SIGN(@Number) AS [Sign of Number];
GO
Output:-
3. If the Expression is negative, the
function returns -1
Example:-
DECLARE @Number As int;
SET @Number = -57.05;
SELECT SIGN(@Number) AS [Sign of -57.05];
GO
Output:-
Based
on this, you can use the SIGN() function to find out whether a value is
negative, null, or positive: simply pass the value (or a variable) to SIGN()
and use a logical operator to check its sign.
Example:-
DECLARE @Side As Decimal(10,3),
@Perimeter As Decimal(10,3),
@Area As Decimal(10,3);
SET @Side =
48.126;
SET @Perimeter =
@Side * 4;
SET @Area =
@Side * @Side;
IF SIGN(@Side) > 0
BEGIN
PRINT 'Square Characteristics';
PRINT '-----------------------';
PRINT 'Side = ' + CONVERT(varchar(10), @Side, 10);
PRINT 'Perimeter = ' + CONVERT(varchar(10), @Perimeter, 10);
PRINT 'Area = ' + CONVERT(varchar(10), @Area, 10);
END;
ELSE
PRINT 'You must provide a positive value';
GO
Output:-
2. The Absolute
Value of a Number:-
The
decimal numeric system counts from minus infinity to infinity. This means that
numbers are usually negative or positive, depending on their position from 0,
which is considered as neutral. In some operations, the number considered will
need to be only positive even if it is provided in a negative format. The
absolute value of a number x is x if the number is (already) positive. If the
number is negative, its absolute value is its positive equivalent. For example,
the absolute value of 12 is 12, while the absolute value of â€“12 is 12.
To
get the absolute value of a number, you can use the ABS() function.
Syntax :-
ABS(Expression)
This
function takes an expression or a number as argument and returns its absolute
value.
Example:-
DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;
SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO
Output:-
3. The Ceiling of a
Number:-
Consider
a decimal value such as 12.155. This number is between integer 12 and integer
13
In
the same way, consider a number such as â€“24.06. As this number is negative, it
is between â€“24 and â€“25, with â€“24 being greater.
In
algebra, the ceiling of a number is the closest integer that is greater than or
higher than the number considered. In the first case, the ceiling of 12.155 is
13 because 13 is the closest integer greater than or equal to 12.155. The
ceiling of â€“24.06 is â€“24.
To
get the ceiling of a number, Transact-SQL provides the CEILING() function.
Syntax :-
CEILING(Expression)
This
function takes as argument a number or an expression that can evaluate to a
number. After the conversion, if the function succeeds, it returns a
double-precision number that is greater than or equal to Expression.
Example:-
DECLARE @Number1 As Numeric(6, 2),
@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
SELECT CEILING(@Number1) AS [Ceiling of 12.155],
CEILING(@Number2) AS [Ceiling of â€“24.06];
GO
Output:-
Example:-
DECLARE @Number1 As Numeric(6, 2),
@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
PRINT 'The
ceiling of 12.155 is ' +
CONVERT(varchar(10), CEILING(@Number1));
PRINT 'The
ceiling of â€“24.06 is ' +
CONVERT(varchar(10), CEILING(@Number2));
GO
Output:-
4. The Floor of a
Number:-
Consider
two decimal numbers such as 128.44 and -36.72. The number 128.44 is between 128
and 129 with 128 being the lower. The number â€“36.72 is between â€“37 and â€“36 with
â€“37 being the lower. The lowest but closest integer value of a number is
referred to as its floor. Based on this, the floor of 128.44 is 128. The floor
of â€“36.72 is â€“37.
To
support finding the floor of a number, Transact-SQL provides the FLOOR()
function.
Syntax:-
FLOOR(Expression)
The FLOOR()
function takes as argument a numeric value or an expression that can be
evaluated to a number. If the function succeeds during its conversion, it
produces the integer that is the floor of the argument.
Example:-
DECLARE @Number1 As Numeric(6, 2),
@Number2 As Numeric(6, 2);
SET @Number1 = 128.44;
SET @Number2 = -36.72;
SELECT FLOOR(@Number1) AS [Floor of 128.44],
FLOOR(@Number2) AS [Floor of â€“36.72];
GO
Output:-
5. The Exponent of a Number:-
To
calculate the exponential value of a number, Transact-SQL provides the EXP()
function.
Syntax:-
EXP(Expression)
This
function takes one argument as a number or an expression that can be evaluated
to a number.
Example:-
DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;
SELECT EXP(@Number) AS [Exponent of 6.48];
GO
Output:-
6. The Power of a Number:-
The power of a number
is the value of that number when raised to another number. This is done using
the following formula:
ReturnValue = x^{y}
To support finding
the power of a number, Transact-SQL provides the POWER() function.
Syntax:-
POWER(x, y)
This function takes
two required arguments. The first argument, x, is used as the base number to be
evaluated. The second argument, y, also called the exponent, will raise x to
this value.
Example:-
DECLARE @x As Decimal(6, 2),
@y As Decimal(6, 2);
SET @x = 20.38;
SET @y = 4.12;
SELECT POWER(@x, @y) AS [Power of 20.38
raised to 4.12];
GO
Output:-
7. The Natural Logarithm of a Number:-
To assist with
finding the natural logarithm of a number, Transact-SQL provides the LOG()
function.
Syntax:-
LOG(Expression)
This function takes
one argument as a number or an expression that can evaluate to a number. After
the calculation, it returns the natural logarithm of the argument.
Example:-
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG(@Number) AS [Natural Logarithm of 48.16];
GO
Output:-
8. The Base-10 Logarithm of a Number:-
To calculate the base
10 logarithm of a number, Transact-SQL provides the LOG10() function.
Syntax :-
LOG10(Expression)
The number to be evaluated
is passed as the argument X. The function returns the logarithm on base 10
using the formula:
y = log10x
which is equivalent
to
x = 10y
Example:-
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG10(@Number) AS [Base-10 Logarithm of 48.16];
GO
Output:-
9. The Square Root:-
To support the
calculation of a square root, Transact-SQL provides the SQRT() function.
Syntax:-
SQRT(Expression)
This function takes
one argument as a positive decimal number. If the number is positive, after the
calculation, the function returns the square root of x.
Example:-
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT SQRT(@Number) AS [The square root of 48.16 is];
GO
Output:-
If the
number is negative, you would receive an error. In this case, you can use a
control statement to find out whether the Expression is positive.
Example:-
DECLARE @Number As Decimal(6, 2);
SET @Number = 258.4062;
IF SIGN(@Number) > 0
PRINT 'The square root of 258.4062 is ' +
CONVERT(varchar(12), SQRT(@Number));
ELSE
PRINT 'You must provide a positive number';
GO
Output:-