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

• In SQL plus it will be different :