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

Microsoft SQL Server Training Online Learning Classes Built in functions Mathematical

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

2 comments:

  1. This blog is very useful for Students , Learned a lot of new technonologies and thanks for sharing
    .Net Online Training

    ReplyDelete