Transact-SQL Built-In Functions

 Arithmetic Functions

 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. Its syntax is:

`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:

• If the Expression is positive, the function returns 1. Here is an example:
 ```DECLARE @Number As int; SET @Number = 24.75; SELECT SIGN(@Number) AS [Sign of 1058]; GO```
• If the Expression is null, the function returns 0
 ```DECLARE @Number As int; SET @Number = 0; SELECT SIGN(@Number) AS [Sign of Number]; GO```
• If the Expression is negative, the function returns -1
 ```DECLARE @Number As int; SET @Number = -57.05; SELECT SIGN(@Number) AS [Sign of -57.05]; GO```

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. Here is an example:

```-- Square Calculation
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```

Here is an example of executing the statement:

 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. Its syntax is:

`ABS(Expression)`

This function takes an expression or a number as argument and returns its absolute value. Here is an example:

```DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;

SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO```
 The Ceiling of a Number

To get the ceiling of a number, Transact-SQL provides the CEILING() function. Its syntax is:

`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. Here is an 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```
 The Floor of a Number

To support finding the floor of a number, Transact-SQL provides the FLOOR() function. Its syntax is:

`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. Here is an 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```
 The Exponent of a Number

To calculate the exponential value of a number, Transact-SQL provides the EXP() function. Its syntax is:

`EXP(Expression)`

This function takes one argument as a number or an expression that can be evaluated to a number. Here is an example:

```DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;

SELECT EXP(@Number) AS [Exponent of 6.48];
GO```

 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 = xy`

To support finding the power of a number, Transact-SQL provides the POWER() function. Its syntax is:

`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. Here is an 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```

This would produce:

 The Natural Logarithm of a Number

To assist with finding the natural logarithm of a number, Transact-SQL provides the LOG() function. Its syntax is:

`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. Here is an example:

```DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT LOG(@Number) AS [Natural Logarithm of 48.16];
GO ```
 The Base-10 Logarithm of a Number

To calculate the base 10 logarithm of a number, Transact-SQL provides the LOG10() function. Its syntax is:

`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`

Here is an example:

```DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT LOG10(@Number) AS [Base-10 Logarithm of 48.16];
GO ```
 The Square Root

To support the calculation of a square root, Transact-SQL provides the SQRT() function. Its syntax is:

`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. Here is an example:

```DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT SQRT(@Number) AS [The square root of 48.16 is];
GO```

This would produce:

If the number is negative, you would receive an error. Here is an example:

In this case, you can use a control statement to find out whether the Expression is positive. Here is an 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```

Here is one example of executing the statement:

Published on Monday 24 December 2007