cast,extract,nullif,coalesce

CAST function:

Description:

------------

CAST converts one built-in datatype or collection-typed value into another

built-in datatype or collection-typed value.

Syntax:

-------

The syntax for the CAST function is:

CAST (expr AS type_name )

SELECT '22-Aug-2003'+1 FROM dual;

SELECT CAST('22-aug-2003' AS DATE) + 1 FROM dual;

SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE)  FROM dual;

SELECT CAST('22-OCT-1997' AS TIMESTAMP)  FROM dual;

SELECT CAST('12' AS NUMBER) * 10 FROM DUAL;

EXTRACT function:

-----------------

Description

The EXTRACT function extracts a value from a date or interval value.

Syntax:

The syntax for the EXTRACT function is:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM { date_value | interval_value } )

SELECT extract(YEAR FROM sysdate) FROM dual;

SELECT extract(MONTH FROM sysdate) FROM dual;

SELECT extract(DAY FROM sysdate) FROM dual;

NOTE: With EXTRACT function we can extract YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,

TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR.

SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 AMERICA/NEW_YORK')

FROM DUAL;

SELECT EXTRACT(TIMEZONE_MINUTE FROM TIMESTAMP '1999-01-01 10:00:00 +5:30')

FROM DUAL;

SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) FROM DUAL;

SELECT * FROM emp WHERE extract(MONTH FROM hiredate) = 12;

SELECT * FROM emp WHERE extract(MONTH FROM hiredate) IN (2,8,12);

SELECT EXTRACT(YEAR FROM DATE '2003-08-22') FROM DUAL;

SELECT EXTRACT(MONTH FROM DATE '2003-08-22') FROM DUAL;

SELECT EXTRACT(DAY FROM DATE '2003-08-22') FROM DUAL;

General Functions

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

SELECT SAL, COMM, SAL + COMM FROM EMP;

SELECT SAL, COMM, SAL - COMM FROM EMP;

SELECT SAL, COMM, SAL * COMM FROM EMP;

SELECT SAL, COMM, SAL / COMM FROM EMP WHERE empno IN(7369, 7499);

NVL, NVL2, NULLIF, COALESCE, CASE, DECODE

NVL - it is useful to substitute the NULL value with some other value.

NVL function will take only two arguments. And the datatype of both the argument

should be same.

Syntax: NVL(expr1, expr2)

-------------------------

If expr1 is having NULL value it will substitute with expr2 value.

SELECT comm, NVL(comm,9) FROM emp;

SELECT sal + comm FROM emp;

SELECT sal, comm, NVL(comm, 0), sal + NVL(comm,0) ts FROM emp;

SELECT COMM, NVL(COMM, 'NO COMM') FROM EMP;

SELECT comm, NVL(TO_CHAR(comm), 'No Comm') FROM emp;

SELECT COMM, NVL(CAST(1234 AS CHAR(10)), 'NO COMM') FROM EMP;

SELECT COMM, NVL(CAST(comm AS CHAR(10)), 'NO COMM') FROM EMP;

SELECT * FROM emp;

NVL2: it will take three arguments. In this function first argument is checked,

if first argument is null then it will display second argument value, if first

argument is null, it will be substituted with the second argument value.

Syntax:

NVL2(expr1, expr2, expr3)

In this NVL2 function second and third argument datatype should be same, first

argument datatype can be different.

SELECT comm, NVL2(comm, 'Earning Comm', 'No Commission') FROM emp;

SELECT comm, NVL2(COMM,1,2) FROM EMP;

SELECT COMM, NVL2(COMM,1,'not earning commission') FROM EMP; - will give error

SELECT COMM, NVL2(COMM,'1','not earning commission') FROM EMP;

SELECT COMM, NVL2(COMM,TO_CHAR(1),'not earning commission') FROM EMP;

NULLIF: This function is useful to compare first expression value with

second expression value, if both are NOT same then it will display first expression

value in the output. if both expression are same then it will display NULL in

the output.

Syntax: NULLIF(expr1, expr2)

SELECT NULLIF(10,20) FROM DUAL;

SELECT NULLIF(2,2) FROM DUAL;

SELECT NULLIF('a','A') FROM DUAL;

SELECT NULLIF(65,'A') FROM DUAL; -- will give error

SELECT NULLIF('65','A') FROM DUAL;

SELECT NULLIF(CHR('65'),'A') FROM DUAL;

COALESCE function: is useful to display the first not null value of the expressions

given in coalesce function. In coalesce function we can give any number of

arguments, and if any argument is having first not null value, that argument value

will be displayed in the output.

In this function all the expressions data type should be same.

Syntax: COALESCE(expr1, expr2, expr3,..........exprn)

Note: this function should have minimum two arguments.

SELECT COALESCE(comm) FROM emp; -- will give error

SELECT comm, sal, COALESCE(comm, sal) FROM emp; -- will EXECUTE successfully

SELECT comm, mgr, sal, COALESCE(COMM,MGR,SAL,0) MD FROM EMP;

SELECT COALESCE(NULL, NULL, NULL, NULL) AS XYZ FROM DUAL;

SELECT COALESCE(NULL, NULL, NULL, 4) AS XYZ FROM DUAL;

SELECT COALESCE(NULL, NULL, 3, 4) AS XYZ FROM DUAL;

SELECT COALESCE(NULL, 2, 3, 4) AS XYZ FROM DUAL;

SELECT COALESCE(1, 2, 3, 4) AS XYZ FROM DUAL;

SELECT COALESCE(1, 2, 3,'NO VALUE') AS XYZ FROM DUAL;  -- WILL GIVE ERROR