SqlTutorial-1 : Lesson-11 : Class-5 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Mathematical functions

INDEX - 23 Methods

7.1. Trigonometric functions

• SIN(), COSE(), TAN(), ASIN(), ACOS(), ATAN()
• DEGREES(PI())
• RADIAN()

7.2. Filtering functions

• CEILING() and FLOOR()
• ROUND()
• ABS()

7.4. Power and Root functions.

• POWER()
• SQUARE()
• SQRT()

7.5. Validating number.

• ISNUMERIC() -
• SIGN() - returns sing of given number

7.6. Generating Random Number

• RAND()

7.3. Logarithmic and Exponential functions

• EXP()
• LOG() & LOG10()
• PI() = 22/7

7.1. Trigonometric functions

1. SIN(), COS(), TAN(), ASIN(), ACOS(), ATAN().

   Syntax

   Any_function(float_expression);

   Examples

   1. Select ROUND(SIN(90),0) = Select ROUND(SIN(DEGREES(PI()/2),2) = 1
   2. Select Cos(0) =>1
   3. Select Tan(PI()/4) =>1

2. Degrees():

   It converts a float value, which represents an angle in radians, into the value of this angle in degrees.
   SELECT DEGREES(PI()/4) =>45

7.2. Filtering functions

1. CEILING():

   Returns the value representing the smallest integer that is greater-than or equal to the input_number.

   Syntax

   CEILING ( input_float )

   Examples:

   1. Select CEILING(-1.25) = - 1
   2. Select CEILING (1.25 = 2

2. FLOOR ():

   Returns the value that represents the largest integer that is less-than or equal to the input_number.

   Syntax

   FLOOR ( input_number )

   Examples:

   1. SELECT 5-2*FLOOR(5/2) => 1
   2. SELECT 1.22 - FLOOR(1.22) => .22 -- Returning the decimal portion
   3. Select FLOOR(-1.25)=> -2
   4. Select FLOOR(1.25) => 1

3. ROUND():

   Rounds a value to the number of decimal places specified in the second parameter.

   Syntax

     ROUND ( numeric_expression, length [,integer_no ] )
   Where
   

   • Length: If +ve, numeric_expression is rounded to then umber of decimal positions specified by length. If –ve, numeric_expression is rounded on the left side of the decimal point.
   • Integer_no: Is the type. Default is 0. If Non-zero is specified,numeric_expression is truncated.

   Examples:

   1. If length is negative and larger than the number of digits before the decimal point, then Round() returns Zero;

      ROUND(748.58,-4) => 0

   2. If length is negative number, Round() returns rounded numeric_expression, regardless of data type.

      1. Select ROUND(748.58,-1) => 750.00 
      2. Select ROUND(748.58,-2) => 700.00 
      3. Select ROUND(748.58,-3) => 1000.00 
      4. Select ROUND(748.58,-4) => 0;

   3. Using ROUND() to truncating

      Select ROUND(150.75,0,1) =>  150.0

   4. A numeric of anything but 0 will mean the number is truncated

      1. SELECT ROUND(100.345678,5,0) =>100.345680
      2. SELECT ROUND(100.345678,5,1) =>100.345670
      3. SELECT ROUND(107.345678,-1,0) => 110.000000
      4. SELECT ROUND(12.5,0) => 13.0
      5. SELECT ROUND(12.4999,0) =>12.0000 
      6. SELECT ROUND(12.4999,1) =>12.5000
      7. SELECT ROUND(12.4999,-1) =>10.0000
      8. SELECT ROUND(3.22245, 3) => 3.22200
   
   

4. ABS():

   Returns the absolute value of any number passed to it.

   Syntax:

   ABS (float_number);

   Examples:

   1. Returns positive value

      Select ABS(-1.25) => 1.24

7.3. Logarithmic and Exponential functions

1. EXP():

   EXP Returns a float, which is the exponential of the float value passed in as a parameter

   Syntax

   EXP (float_number);

   Examples:

   SELECT EXP(4) => 54.598150033144236

2. LOG():

   It takes one positive valued parameter, and returns the natural logarithm of this parameter as a float value

   Syntax 

   LOG(Decimal_Number)

   Examples:

   1. Find logramethic value

      SELECT LOG(5.67) => 1.7351891177396608S

   2. LOG value is reverse to Exponential

      SELECT LOG(EXP(2)) => 2

3. LOG10():

   Returns the base 10 logarithm of this parameter as a float value

   Syntax

   LOG10(float) ;

   Examples:

   1. Find the LOG10 value

      SELECT LOG10(5.67) => 0.75358305889290655

4. PI() :

   Returns the value of the mathematical constant PI to 16 decimal places

   Syntax

   PI()

   Examples:

   1. Find the PI Value

      SELECT PI()=>3.14159265358979315 

7.4. Power and Root functions.

1. POWER(x,y) :

   Returns a number(x) raised to the power of a number (y)

   Syntax

   POWER (float_x, int_n);

   Examples

   1. Find Square(10)

      SELECT POWER(10,2)=>100

2. Square():

   Returns the square of the given number

   Syntax

   SQUARE(float_number);

   Examples

   1. Find SQuare(2)

      SELECT SQUARE(20)=> 400

   2. SQRT(n) is reverse of SQUARE(n)

      SELECT SQRT(SQUARE(2))=> 2

3. Sqrt():

   Returns square root of the given number

   Syntax

   Sqrt(float_number);

   Examples

   1. Find the SquareRoot of 100

      SELECT SQRT(100) => 10

   2. Find the SQRT of decimal value

      Select SQRT(125.43) => 11.199553562530964

7.5. Validating number.

1. ISNUMERIC():

   Returns 1 if true otherwise 0

   Syntax

   ISNUMBERIC(String)

   Examples

   1. Validating negative decimal value

      Select ISNUMERIC(-1.25)=> 1

   2. Validating negative String Value

      Select ISNUMERIC('SQL Server') => 0

   3. Validating Date value

      Select ISNUMERIC('2001-09-30') =>0

   4. Validating Alphanumeric value

      SELECT ISNUMERIC('123ABC') => 0

   5. Validating numeric-String value

      LECT ISNUMERIC('123')=>1

2. SIGN():

   Returns sing of given number. i.e., -1 for negative +1 for positive and 0 for zero.

   Syntax

   SIGN(float_number);

   Examples

   1. Find sign of negative integer

      SELECT SIGN(-23)=> -1

7.6. Generating Random Number

1. RAN():

   Generates a random number between 0 and 1 as  float point value.

   Syntax:

   RAND ( [ seed ] )

   Examples:

   1. Generate floating number 0-1

      SELECT RAND()=> 0.16134333864034361

   2. Generate integer 1-100

      SELECT CEILING(RAND()*100)=> 51