This content originally appeared on DEV Community and was authored by technonotes-hacker
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 :
This content originally appeared on DEV Community and was authored by technonotes-hacker
technonotes-hacker | Sciencx (2024-11-06T04:33:49+00:00) SQL – Dia 10. Retrieved from https://www.scien.cx/2024/11/06/sql-dia-10/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.