Num,trunc ,Ceil,floor, mod, abs ,sqr,power

Number Functions

================

SELECT ROUND(20.985, 2) FROM DUAL;

SELECT ROUND(20.984, 2) FROM DUAL;

SELECT ROUND(20.9854367, 3) FROM DUAL;

SELECT ROUND(20.9858367, 3) FROM DUAL;

SELECT ROUND(20.684, 2) FROM DUAL;

SELECT ROUND(20.684, 1) FROM DUAL;

SELECT ROUND(20.584, 1) FROM DUAL;

SELECT ROUND(20.584,0) FROM DUAL;

SELECT ROUND(20.484,0) FROM DUAL;

SELECT ROUND(20.484) FROM DUAL;

SELECT ROUND(20.584) FROM DUAL;

SELECT ROUND(20.584,0) FROM DUAL;

SELECT ROUND(24.584,-1) FROM DUAL;

SELECT ROUND(25.584,-1) FROM DUAL;

SELECT ROUND(5525.584,-2) FROM DUAL;

SELECT ROUND(5565.584,-2) FROM DUAL;

SELECT ROUND(5565.584,-3) FROM DUAL;

SELECT ROUND(5365.584,-3) FROM DUAL;

SELECT ROUND(5365.584,-4) FROM DUAL;

SELECT ROUND(4365.584,-4) FROM DUAL;

SELECT ROUND(-2.7) FROM dual;

SELECT ROUND(-2.5) FROM dual;

SELECT ROUND(-2.4) FROM dual;

SELECT ROUND(-2.4,1) FROM dual;

SELECT ROUND(247.489) FROM dual;

TRUNC function:

Description:

The TRUNC function returns a number truncated to a certain number of decimal places.

Syntax (with numbers):

The syntax for the TRUNC function is:

TRUNC( number, [ decimal_places ] )

Examples:

SELECT TRUNC(20.988, 2) FROM DUAL;

SELECT TRUNC(20.994, 1) FROM DUAL;

SELECT TRUNC(20.944, 1) FROM DUAL;

SELECT TRUNC(20.994, 0) FROM DUAL;

SELECT TRUNC(20.994) FROM DUAL;

SELECT TRUNC(455.994, -1) FROM DUAL;

SELECT TRUNC(455.994, -2) FROM DUAL;

SELECT TRUNC(455.994, -3) FROM DUAL;

SELECT TRUNC(1455.994, -3) FROM DUAL;

SELECT TRUNC(20.99) FROM DUAL;

SELECT TRUNC(-3.2) FROM DUAL;

SELECT TRUNC(-4.99) FROM DUAL;

SELECT TRUNC(247.589) FROM dual;

CEIL function:

Description:

The CEIL function returns the integer value that is greater

than or equal to a number.

Syntax:

The syntax for the CEIL function is:

CEIL( number )

Examples:

SELECT CEIL(-20.99) FROM DUAL;

SELECT ROUND(-20.99) FROM DUAL;

SELECT CEIL(20.99) FROM DUAL;

SELECT CEIL(20.35) FROM DUAL;

SELECT ROUND(20.35) FROM DUAL;

SELECT CEIL(20.1) FROM DUAL;

SELECT CEIL(20) FROM DUAL;

FLOOR function:

Description:

The FLOOR function returns the integer value that is equal

to or less than a number.

Syntax:

The syntax for the FLOOR function is:

FLOOR( number )

Examples:

SELECT FLOOR(-20.99) FROM DUAL;

SELECT FLOOR(20.99) FROM DUAL;

SELECT FLOOR(20.35) FROM DUAL;

SELECT FLOOR(20) FROM DUAL;

SELECT FLOOR(-20.01) FROM DUAL;

SELECT CEIL(20.35), FLOOR(20.35) FROM DUAL;

MOD function:

Description:

The MOD function returns the remainder of m divided by n.

Syntax:

The syntax for the MOD function is:

MOD( m, n )

Examples:

SELECT mod(5,2) FROM dual;

SELECT mod(5,11) FROM dual;

SELECT * FROM EMP;

SELECT * FROM emp WHERE mod(empno,2)=0;

SELECT * FROM emp WHERE mod(empno,2)=1;

SELECT * FROM EMP;

SELECT * FROM EMP WHERE MOD(LENGTH(ENAME),2)=0;

SELECT ENAME, SYSDATE, HIREDATE,ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE), 2) DIFF

FROM EMP WHERE ENAME='SCOTT';

ABS function:

Description:

The ABS function returns the absolute value of a number.

Syntax:

The syntax for the ABS function is:

ABS( number )

Examples:

SELECT ABS(-9) FROM dual;

SELECT ABS(-9.5) FROM DUAL;

SELECT ABS(-99.456) FROM DUAL;

SELECT ENAME, SYSDATE, HIREDATE, ABS(ROUND(MONTHS_BETWEEN(HIREDATE, SYSDATE),2)) DIFF

FROM EMP WHERE ENAME='MARTIN';

create table abs_demo(sal number(4));

insert into abs_demo values(&sal);

select * from abs_demo;

select ABS(sal) * .3 as tax from abs_demo;

SQRT function:

Description:

The SQRT function returns the square root of n.

Syntax:

The syntax for the SQRT function is:

SQRT( n )

Note: n is a positive number.

SELECT sqrt(25) FROM dual;

SELECT sqrt(-25) FROM dual;

SELECT sqrt(ABS(-25)) FROM dual;

SELECT SQRT(50) FROM DUAL;

SELECT SQRT(49) FROM DUAL;

SELECT SQRT(2500) FROM DUAL;

--dt.07-MAR-2014 –

POWER function:

Description:

The POWER function returns m raised to the nth power.

Syntax:

The syntax for the POWER function is:

POWER( m, n )

Parameters or Arguments:

m is the base.

n is the exponent.

Note:

If m is negative, then n must be an integer

SELECT POWER(2,2) FROM DUAL;

2 * 2

SELECT POWER(4,3) FROM DUAL;

4 * 4 * 4

SELECT POWER(-3,2) FROM dual;

-3 * -3

SELECT POWER(-3,3) FROM dual;

-3 * -3 * -3

SELECT POWER(2,-2) FROM DUAL;

select 1/2*2 from dual;

SELECT POWER(2, -3) FROM DUAl;

select 1/2*2*2 FROM DUAl;

SELECT POWER(-2,-2) FROM DUAL;

SELECT POWER(-2,-3) FROM DUAL;

SELECT SQRT(9) FROM DUAL;

SELECT 9/3 FROM DUAl;

SELECT SQRT(4) FROM DUAL;

SELECT 4/2 FROM DUAL;

SELECT SQRT(-4) FROM DUAL;

SELECT SQRT(ABS(-4)) FROM DUAl;

SELECT POWER(3,2) NUM FROM DUAL;

SELECT POWER(&A,2) NUM FROM DUAL;

SELECT ABS(&A) NUM FROM DUAL;

SELECT SQRT(POWER(&a, 2)) num FROM DUAL;

first half of ename in upper case and second half in lower case

eg: WARD  WArd      SMITH SMIth

SELECT ENAME FROM EMP;

select ename, substr(ename, 1, round(length(ename)/2)) from emp;

SELECT ename,

CONCAT(UPPER(SUBSTR(ENAME,1,ROUND(LENGTH(ENAME)/2))),

LOWER(SUBSTR(ENAME,ROUND(LENGTH(ENAME)/2)+1)) ) NEW_NAME

FROM emp;

select ename, upper(substr(ename,1, round(length(ename)/2))),

lower(substr(ename, round(length(ename)/2)+1)) from emp;

SELECT ENAME,

CONCAT(UPPER(SUBSTR(ENAME,1,ROUND(LENGTH(ENAME)/2))),

LOWER(SUBSTR(ENAME,-1*TRUNC(LENGTH(ENAME)/2))) ) NEW_NAME

FROM emp;

select ename, concat(upper(substr(ename, 1, round(length(ename)/2))),

lower(substr(ename, trunc(length(ename)/2)*-1))) from emp;

SELECT REPLACE('itamerica', 'a', 'u') from dual;

SELECT

REPLACE(ename, SUBSTR(ename, ROUND(LENGTH(ename)/2)+1), LOWER(SUBSTR(ename, ROUND(LENGTH(ename)/2)+1)))

FROM emp;

Conversion Functions

====================

TO_CHAR(), TO_NUMBER(), TO_DATE(), TO_TIMESTAMP(), TO_TIMESTAMP_TZ(),

CAST( )

SELECT * FROM emp;

20000      $20,000.00

01-NOV-13  November, 01 2013

SELECT $2,000 * 12 FROM emp;