Various Numeric Functions are avaialbe in Teradata. For example: SQRT, POWER, MOD, ABS, EXP, LN, LOG, RANDOM, ROUND, FLOOR, CEILING

Teradata Numeric Functions

• SQRT
• POWER
• MOD
• ABS
• SIGN
• EXP
• LN
• LOG
• RANDOM
• ROUND
• FLOOR
• CEILING
• NULLIFZERO
• ZEROIFNULL
• GREATEST
• LEAST

• SQRT: Returns the square root of the given positive number.

  
  SELECT SQRT(81);
  Output: 9
  

  
  
• POWER: Returns base_value raised to the power of given argument(power_component).

  
  Syntax: POWER(base_value , power_component)
  
  SELECT POWER(9,2);
  Output: 81
  
  SELECT 9**2 ;    -- Equivalent to POWER(9,2);
  Output: 81
  

  
  
• MOD: Returns the remainder from a division operation.

  
  Syntax: Main_Number MOD Divisor_number
  
  SELECT 26 MOD 5;
  Output: 1
  

  
  
• ABS: Returns the absolute value of number.

  
  Syntax: ABS(number_value)
  
  SELECT ABS(500);
  Output: 500
  
  SELECT ABS(-500);
  Output: 500
  
  SELECT ABS(+500);
  Output: 500
  

  
  
• SIGN: Returns the -1 if value passed in negative, returns 1 if value is positive and greater than 0 and returns Zero(0) if the value is zero.

  
  Syntax: SIGN(number_value)
  
  SELECT SIGN(-2);
  Output:  -1
  
  SELECT SIGN(2);
  Output:  1
  
  SELECT SIGN(0);
  Output:  0
  

  
  
• EXP: Raises e to the power of given number [Value of e=2.718].

  
  Syntax: EXP(number_value)
  
  SELECT EXP(3);
  Output: 20.0855369232
  

  
  
• LN: Returns natural logarithm of given number.

  
  Syntax: LN(number_value)
  
  SELECT LN(7.387524);
  Output: 1.99979263145790E 000
  

  
  
• LOG: Returns the base 10 logarithm of given number.

  
  Syntax: LOG(number_value)
  
  SELECT LOG(100);
  Output: 2.00000000000000E 000
  

  
  
• RANDOM: Returns random values between mentioned start range to end range.

  
  Syntax: RANDOM(start_range , end_range)
  
  SELECT RANDOM(1,100);
  Output: 10
  

  
  
• ROUND: Returns rounded values of the passed decimal number upto the given places.

  
  Syntax: ROUND(given_decimal,[places_value])
  
  SELECT ROUND(35.222,1);
  Output: 35.200
  
  SELECT ROUND(10.23,0);
  Output: 10.00
  
  SELECT ROUND(10.63,0);
  Output: 11.00
  
  SELECT ROUND(65.63,-2);
  Output: 100.00
   
  SELECT ROUND(65.63,-3);
  Output: 0.00
  

  
  
• FLOOR: Returns largest integer equal to or less than given parameter.

  
  Syntax: FLOOR(number_value)
  
  SELECT FLOOR(55.63);
  Output: 55.00
  
  SELECT FLOOR(-55.63);
  Output: -56.00 
  

  
  
• CEILING: Returns smallest integer that is not less than given parameter.

  
  Syntax: CEILING(number_value)
  
  SELECT CEILING(55.63);
  Output: 56.00
  
  SELECT CEILING(-55.63);
  Output: -55.00 
  

  
  
• NULLIFZERO: Returns NULL if the passed argument is Zero.

  
  SELECT NULLIFZERO(0);
  Output: NULL
  

  
  
• ZEROIFNULL: Returns Zero if the passed argument is NULL.

  
  SELECT ZEROIFNULL(NULL);
  Output: 0
  

  
  
• GREATEST: Returns greatest number from the passed parameters.

  
  select GREATEST(12,434,21,543,243,111) greator_num;
  Output: 543
  

  
  
• LEAST: Returns smallest number from the passed parameters.

  
  select LEAST(12,434,21,543,243,111) smaller_num;
  Output: 12