Mathmatical Function in Sql Sever

Mathmatical Function in Sql Sever:-

• Abs
• Ceiling
• Floor
• Power
• Rand
• Square
• Sqrt
• Round

1.Abs (numeric_expressions)- Abs stands for absolute and returns, the absolute (positive) number.

Syntax- select  ABS(numeric_expression)

Ex-

---Abs functions

select ABS(011) ---return 011

select ABS(-101)--return 101 not include minus sign

select ABS(111.1)---return 111.1

2. Ceiling() & Floor() functions:-Ceiling and Floor functions accept a numeric expression as a single parameter . Ceiling () returns a smallest integer value greater than or equal to the parameter, whereas Floor() returns the largest integer less than or eual to the parameter.

Syntax-    select CEILING(numeric_expression)

    select floor(numeric_expression)

Ex- 

·         select CEILING(12.1) ---returns output 13

·         select CEILING(-12.1) ---returns output -12

·         select floor(12.1) ---returns output 12

·         select floor(-12.1) ---returns output 13

  
3. Power():-  Returns the power value of the specified expression to the specified power.

Syntax:- select POWER(numeric_expression)

Ex-  select POWER(3,2) ---returns output 9

4. Square():- returns the  square of the given number.

Syntax:-  select SQUARE(numeric_expression)

Ex-  select SQUARE(4)---returns output 16

5. Sqrt():-  Returns the square root of the given number.

Syntax:-  select SQRT(numeric_expression)

Ex-  select SQRT(2)---returns sqareroot output 1.4142135623731

6.Random():- Return the random float number between 0 and 1. Rand() function takes an optional seed parameter. when seed value is supplied the RADN() function always returns the same value for the same seed.

Syntax- select RAND([seed_value])

Ex-  select RAND()---returns always 0 to 1 values but almost 0

select rand(1100)---returns output 0.734069628625685

---if we want 1 to 100 number we use random number:

Ex- select (RAND() * 100) --return 0 to 100 (72.1944369495552)

---if we want only integer no float value----

Ex- select floor(RAND() * 100.9)  --return 0 to 100 (72.1944369495552)

7.ROUND() function(numeric_expression, length[function]):- Rounds the given numeric expression based on the given length. This function takes 3 parameters.

1.  Numeric_Expression-: it’s a number that we want to round.
2.  Length:- Length parameters, specify the number of the digits that we want to round do. If the length is a positive number, then the rounding is applied to the number before the decimal.
3. The Optional Function Parameter it used to indicate rounding or truncation operations. 0 indicates rounding , non zero indicates truncation. Default, if it specified is 0.

Ex-:

--if Round 2 places after (to the right)the decimal point

select round(850.556,2)

---output 850.560 (2 decimal values after ignore points indicate 0)

select ROUND(850.556,1)

--output 850.600 (1 decimal values after ignore points indicate 0)

select ROUND(850.556,3)

--output 850.556(3 decimal values after ignore points indicate 0)

--Truncate output 850.556(3 decimal values after ignore points indicate 0)

select ROUND(850.556,1,1)--returns 1 take in left at point side output(850.500)

select ROUND(850.556,2,1)--returns 2 take in left at point side output(850.550)

select ROUND(850.556,3,1)--returns 3 take in left at point side output(850.556)

select ROUND(850.556,-2)---output 850.23260

select ROUND(9069.556,-3)---output 9000.000

select ROUND(850.556,-2)---output 900.000