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

5 comments:

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

    ReplyDelete
  2. Nice blog. Explained well. I have suggested to my friends to go through this blog. Very nice explanation. Thank you for sharing this useful information. keep sharing.If you want to learn Dotnet course in online, please visit below site.
    dotnet Online Training, dotnet course, dotnet online training in kurnool, dotnet online training in hyderabad, dotnet online training in bangalore, online courses, online learning, online education, trending courses, best career courses

    ReplyDelete
  3. The information which you have provided is very good. It is very useful who is looking for salesforce Online Course Bangalore

    ReplyDelete

  4. Nice blog..! I really loved reading through this article. Thanks for sharing such
    a amazing post with us and keep blogging...

    ms dotnet training in hyderabad

    ReplyDelete