SQL Numeric Functions

SQL built-in String functions

Summary

  • Numeric functions perform manipulation of numbers that normally are contained in a table column.
  • There is a differentiated range of built-in numeric functions in the various RDBMS, yet are there some functions which have the same name and executes the same.
  • There are also functions that perform the same but does not have the same name in different RDBMS.
  • Here is a collection of the most important built-in numeric functions:
    Function RDBMS Description
    ABS() SQL server, MySQL, Oracle, PostgreSQL A mathematical function that returns the absolute (positive) value of the specified numeric expression.
    CEIL() MySQL, PostgreSQL, Oracle Returns the smallest integer greater than, or equal to, the specified numeric expression.
    CEILING() SQL server, MySQL, PostgreSQL
    EXP() SQL server, MySQL, Oracle, PostgreSQL Returns the value of e (the base of natural logarithms) raised to the power of X.
    FLOOR() SQL server, MySQL, Oracle, PostgreSQL Returns the largest integer less than or equal to the specified numeric expression.
    LN() MySQL, PostgreSQL, Oracle Returns the natural logarithm of the specified float expression.
    LOG() MySQL, SQL server
    POWER() SQL server, MySQL, Oracle, PostgreSQL Returns the value of the specified expression to the specified power.
    ROUND() SQL server, MySQL, Oracle, PostgreSQL Returns a numeric value, rounded to the specified length or precision.
    SIGN() SQL server, MySQL, Oracle, PostgreSQL Returns the positive (+1), zero (0), or negative (-1) sign of a numeric expression.
    SQRT() SQL server, MySQL, Oracle Returns the square root of a nonnegative numeric expression

ABS() function

A mathematical function that returns the absolute (positive) value of the specified numeric expression.
Function syntax:
ABS(numeric_expression )
ABS Example:
SQL server, MySQL, Oracle, PostgreSQL:
select isbn_no, price-35 No_ABS, 
ABS(price-35) With_ABS
 from bookstore   
where price-35 <0; 
The result should be:
isbn_no no_abs with_abs
0471777781 -3 3
0596009747 -3 3
1861006314 -6 6

CEIL()/CEILING() function

Returns the smallest integer greater than, or equal to, the specified numeric expression.
MySQL, PostgreSQL and Oracle Function syntax:
CEIL(numeric_expression )
MySQL, PostgreSQL and SQL server Function syntax:
CEILING(numeric_expression )
CEIL Example1:
MySQL, PostgreSQL, Oracle:
select ISBN_NO, price, 
price+0.4 NewPrice ,
CEIL(price+0.4) NewPrice_CEIL
 from bookstore   
where price >30;   
CEILING Example2:
MySQL, PostgreSQL, SQL server:
select ISBN_NO, price, 
price+0.4 NewPrice ,
CEILING(price+0.4) NewPrice_CEIL
 from bookstore   
where price >30;   
The result should be for both examples:
isbn_no price newprice newprice_ceil
0201703092 39 39.4 40
0471777781 32 32.4 33
0596009747 32 32.4 33
0672325764 49 49.4 50
0764557599 42 42.4 43
0764579088 35 35.4 36
1861002025 38 38.4 39

EXP() function

Returns the value of e (the base of natural logarithms) raised to the power of X.
Function syntax:
EXP(float_expression )
EXP Example:
SQL server, MySQL:
select isbn_no, price , 
LOG(price ) , EXP(LOG(price ))
 from bookstore   
where price >34;
EXP Example:
Oracle, PostgreSQL:
select isbn_no, price , 
LN(price ) , EXP(LN(price ))
 from bookstore   
where price >34;
The result should be for both examples:
isbn_no price log(price ) exp(log(price ))
0201703092 39 3.66356164612965 39
0672325764 49 3.89182029811063 49
0764557599 42 3.73766961828337 42
0764579088 35 3.55534806148941 35
1861002025 38 3.63758615972639 38

FLOOR() function

Returns the largest integer less than or equal to the specified numeric expression.
Function syntax:
FLOOR(float_expression )
FLOOR Example:
SQL server, MySQL, Oracle, PostgreSQL:
select isbn_no, price+0.6 No_FLOOR, 
FLOOR(price+0.6) With_FLOOR
 from bookstore   
where price >34; 
The result should be:
isbn_no no_floor with_floor
0201703092 39.6 39
0672325764 49.6 49
0764557599 42.6 42
0764579088 35.6 35
1861002025 38.6 38

LN()/LOG() function

Returns the natural logarithm of the specified float expression. The inverse of this function is EXP().
MySQL, PostgreSQL and Oracle LN() function syntax:
LN(float_expression )
SQL server LOG() function syntax:
LOG(float_expression [, base ] )
MySQL server LOG() function syntax:
LOG([base, ] float_expression )
LN Example1:
MySQL, PostgreSQL, Oracle:
select isbn_no, price , 
LN(price ) , EXP(LN(price ))
 from bookstore   
where price >34;
The result should be:
isbn_no price ln(price ) exp(ln(price ))
0201703092 39 3.66356164612965 39
0672325764 49 3.89182029811063 49
0764557599 42 3.73766961828337 42
0764579088 35 3.55534806148941 35
1861002025 38 3.63758615972639 38
LN Example2:
MySQL, SQL server:
select isbn_no, price , 
LOG(price ) , EXP(LOG(price ))
 from bookstore   
where price >34;
The result should be:
isbn_no price log(price ) exp(log(price ))
0201703092 39 3.66356164612965 39
0672325764 49 3.89182029811063 49
0764557599 42 3.73766961828337 42
0764579088 35 3.55534806148941 35
1861002025 38 3.63758615972639 38

POWER() function

Returns the value of the specified expression to the specified power.
Function syntax:
POWER(float_expression ,power)
POWER Example:
SQL server, MySQL, Oracle, PostgreSQL:
SELECT isbn_no, item_qty, price, 
POWER(price,2) NewPrice,
item_qty*POWER(price,2) NewCost
 FROM order_books;
The result should be:
isbn_no item_qty price newprice newcost
0201703092 2 39 1521 3042
0764557599 1 42 1764 1764
1861002025 5 38 1444 7220
1861006314 2 29 841 1682
0672325764 1 49 2401 2401
0764557599 2 42 1764 3528
0764579088 4 35 1225 4900
0201703092 10 39 1521 15210
0471777781 2 32 1024 2048
1861002025 5 38 1444 7220
1861006314 1 29 841 841

ROUND() function

Returns a numeric value, rounded to the specified length or precision.
Function syntax:
ROUND(numeric_expression , length )
Length is the precision to which numeric_expression is to be rounded. Length can be negative to cause Length digits left of the decimal point of the numeric_expression to become zero.
ROUND Example:
SQL server, MySQL, Oracle:
SELECT isbn_no, price,  price*1.077,
ROUND( price*1.077,1) NewPrice
 FROM bookstore
where price >0;
PostgreSQL:
SELECT isbn_no, price,  price*1.077,
ROUND( (price*1.077)::numeric,1) NewPrice
 FROM bookstore
where price >0;
The result should be for both examples:
isbn_no price price*1.077 newprice
0201703092 39 42.003 42.0
0471777781 32 34.464 34.5
0596009747 32 34.464 34.5
0672325764 49 52.773 52.8
0764557599 42 45.234 45.2
0764579088 35 37.695 37.7
1861002025 38 40.926 40.9
1861006314 29 31.233 31.2

SIGN() function

Returns the positive (+1), zero (0), or negative (-1) sign of a numeric expression.
Function syntax:
SIGN(numeric_expression)
SIGN Example:
SQL server, MySQL, Oracle, PostgreSQL:
select isbn_no, price, price-35, 
SIGN(price-35) 
 from bookstore   
where price >0; 
The result should be:
isbn_no price price-35 sign(price-35)
0201703092 39 4 1
0471777781 32 -3 -1
0596009747 32 -3 -1
0672325764 49 14 1
0764557599 42 7 1
0764579088 35 0 0
1861002025 38 3 1
1861006314 29 -6 -1

SQRT() function

Returns the square root of a nonnegative numeric expression.
Function syntax:
SQRT(numeric_expression)
SQRT Example:
SQL server, MySQL, Oracle:
SELECT isbn_no, item_qty, price, 
ROUND(SQRT(price*price+20*20),1) NewPrice
 FROM order_books;   
PostgreSQL:
SELECT isbn_no, item_qty, price, 
ROUND(SQRT(price*price+20*20)::numeric,1) NewPrice
 FROM order_books;   
The result should be for both examples:
isbn_no item_qty price newprice
0201703092 2 39 43.8
0764557599 1 42 46.5
1861002025 5 38 42.9
1861006314 2 29 35.2
0672325764 1 49 52.9
0764557599 2 42 46.5
0764579088 4 35 40.3
0201703092 10 39 43.8
0471777781 2 32 37.7
1861002025 5 38 42.9
1861006314 1 29 35.2
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.