SQL - Dia 10

Null Functions

nvl
nvl2
nullif
coalease

select 'NULL',null,'',' ' from dual;

Only 2 & 3 output are the NULL values.

nvl

if the value is NULL then print the value in the right hand side value.
Only two arguments are passed. ( Input , what to replace )

select nvl('',0) from dual; select nvl(NULL,0) , nvl(NULL , 'A') from dual;

As the left side argument is NOT null then it prints the same value.

nvl2

3 arguments.
( 'infosys' , 2 , 'B') --> If the first argument is NULL then 3rd arguments will be printed or else second argument will be printed.

select nvl2('A',0,4) from dual; select nvl2('',0,4) from dual;

nullif

two arguments
both should be NULL.

select nullif(1,7) , nullif('aa','aa') from dual; select nullif(1,7) , nvl(nullif('aa','aa'),'A') from dual;

coalesce

only one row.
It prints first NOT NULL Value.
It accepts n no of arguments, no limit.

select NULL, NULL , 30 , 40 , NULL from dual;
select coalesce(NULL, NULL , 30 , 40 , NULL) from dual;

Conversion Function

to_char
to_number
to_date
to_timestamp

to_char

select sysdate from dual ; select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;

--select sysdate from dual ; select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ; select to_char(sysdate,'MONTH'),to_char(sysdate,'MONTH'),to_char(sysdate,'Month'),to_char(sysdate,'MM') from dual ;

select to_char(sysdate,'YEAR'),to_char(sysdate,'Year'),to_char(sysdate,'yyyy'),to_char(sysdate,'yy') from dual ;

select to_char(sysdate,'IW'),to_char(sysdate,'W'),to_char(sysdate,'Q') from dual ;

To Number

--select '123' , 123 , to_number('123') from dual ; - how to check its converted to number ? select '123' , 123 , reverse (to_number('123')) from dual ;

to_date

select '2024/12/24' , to_date('2024/12/24', 'yyyy/mm/dd') from dual ; select to_date('24-DEC-24', 'MON-YYYY-DD') from dual ; --> Error select to_date('DEC-2024-24', 'MON-YYYY-DD') from dual ;

to_timestamp

select TO_TIMESTAMP('DEC-2024-24 16:24:00', 'MON-YYYY-DD HH24:MI:SS') from dual ;

Note :

Anything which is given within single quotes is always STRING.
Joins , sub query , group functions , Analytics Functions & Set Operators are very import in SQL.
what is Index , global temp table , Objects , views , sequence ( why we use ? )
Reverse function will work only for STRING not for Numbers.
what is the dataype of NULL ? Its NULL only.
select sysdate , systimestamp from dual; --> Check this