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 )
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.