Wednesday, October 23, 2013

Conversion Functions in Oracle




---------- Decimal Indicator ----------
SELECT 1234, TO_CHAR(1234,'9999D99') FROM DUAL;
SELECT 1234, TO_CHAR(1234,'999D99') FROM DUAL;

---------- Specific Notation Indicator ----------
SELECT 1234, TO_CHAR(1234,'9.9EEEE'), TO_CHAR(1234,'9.99EEEE') FROM DUAL;

---------- Group Seperator --------------------
SELECT 1234567, TO_CHAR(1234567,'99G99G999') FROM DUAL;
SELECT SAL,TO_CHAR(SAL,'99G999D99') FROM ISC_EMP;

---------- Local Currency Indicator -----------
SELECT 1234, TO_CHAR(1234,'L9999') FROM ISC_EMP;
SELECT SAL, TO_CHAR(SAL,'L999999') CURRENCY FROM ISC_EMP;
SELECT SAL, TO_CHAR(SAL,'L99G999D99','NLS_CURRENCY = RS') Sal FROM ISC_EMP WHERE DEPTNO = 20;

---------- Trailing Minus Indicator ----------
SELECT -10000, TO_CHAR(-10000,'L99G999D99MI') FROM DUAL;
SELECT SAL,COMM,TO_CHAR(SAL-COMM,'L999999MI'),LENGTH(TO_CHAR(SAL-COMM,'L999999MI')) FROM ISC_EMP;

--------- Negitive Number Indicator ----------
SELECT TO_CHAR(-1000,'L99G999D99PR') FROM DUAL;
SELECT SAL,COMM,TO_CHAR(SAL-COMM,'L99G999D99PR') FROM ISC_EMP;

-------- Roman Number Indicator -------------
SELECT 1000,TO_CHAR(11,'RN'),TO_CHAR(10,'rn') FROM DUAL;

-----------Sign Indicator -------------------
SELECT 1000, to_char(1000,'S9999'), to_char(-1000,'S9999') FROM DUAL;
SELECT 1000, to_char(1000,'9999S'), to_char(-1000,'9999S') FROM DUAL;
SELECT SAL, to_char(SAL,'S99999'), to_char(SAL,'999999S') FROM EMP;
SELECT SAL, COMM, TO_CHAR(COMM-SAL,'S99999'), TO_CHAR(COMM-SAL,'99999S') FROM ISC_EMP;

-------- Hexa Decimal Indicator -------------
SELECT 1000, TO_CHAR(1000,'XXXX') FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'XXXX') FROM ISC_EMP;

------- Group Sepearator --------------------
SELECT 10000,TO_CHAR(10000,'99,999.99') FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'99,999.99') FROM ISC_EMP;

---------- Decimal Indicator ---------------
SELECT 10000,TO_CHAR(10000,'L99,999.99') FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'L99,999.99') FROM ISC_EMP;

--------- Dollar Indicator ------------------
SELECT 10000,TO_CHAR(10000,'$99,999.99') FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'$99,999.99') FROM ISC_EMP;

--------- Zero Indicator ------------------
SELECT 1000, TO_CHAR(1000,'0999999'), TO_CHAR(1000,'09999990') FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'$099,999,99') FROM ISC_EMP;

--------- ISO Currency Indicator ----------
SELECT 1000,TO_CHAR(1000,'C9999.99') FROM DUAL;
SELECT ENAME,SAL, TO_CHAR(SAL,'C9999.99') FROM ISC_EMP;

--------- AD/BC Indicator ------------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'AD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'B.C.'), TO_CHAR(SYSDATE,'A.D.') FROM DUAL;
SELECT ENAME, SAL, HIREDATE,TO_CHAR(HIREDATE,'A.D.') FROM ISC_EMP;

----------- MERIDIAN INDICATOR -------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'A.M.'),TO_CHAR(SYSDATE,'PM') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'AM') FROM ISC_EMP;

------------ CENTURY INDICATOR -------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'CC-AD') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'CC-AD') FROM ISC_EMP;

------------- Numeric Week Day Indicator ---
SELECT SYSDATE,TO_CHAR(SYSDATE,'D') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'D') FROM ISC_EMP;

------------ WEEK DAY SPELLING INDICATOR ---------
SELECT SYSDATE,TO_CHAR(SYSDATE,'DAY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'DAY') FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DAY') = 'WEDNESDAY';

------------ Month Day Indicator ---------------
SELECT SYSDATE,TO_CHAR(SYSDATE,'DD-DAY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'DD') FROM EMP;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'DD-DAY') FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DD-DAY') = '17-WEDNESDAY';

------------ Year Day Indicator --------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'DDD') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'DDD') FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DD-DAY') = '17-WEDNESDAY';

------------ Abbreviated week day ------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'DY') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'D-DY-DAY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'D-DY-DAY') FROM ISC_EMP;

-------------4 DIGIT YEAR INDICATOR --------
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YY') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'YYYY') FROM ISC_EMP;

----------- WEEK OF THE MONTH INDICATOR ------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'W') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'W') FROM ISC_EMP;

----------- YEAR WEEK INDICATOR ---------
SELECT SYSDATE, TO_CHAR(SYSDATE,'WW') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'WW') FROM ISC_EMP;

---------- Quarter of the year indicator ------
SELECT SYSDATE, TO_CHAR(SYSDATE,'Q') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'Q') FROM ISC_EMP;

----------JULIAN DAY---------------
SELECT SYSDATE, TO_CHAR(SYSDATE,'J') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'J') FROM ISC_EMP;

---------- Numeric Month Indicator -----
SELECT SYSDATE, TO_CHAR(SYSDATE,'MM') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MM') FROM ISC_EMP;

---------- ABBREIVATED Month Indicator -----
SELECT SYSDATE, TO_CHAR(SYSDATE,'MON') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MON') FROM ISC_EMP;

----------  Month Spelling Indicator -----
SELECT SYSDATE, TO_CHAR(SYSDATE,'MON') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MON') FROM ISC_EMP;

----------- Hours Minutes Seconds --------
SELECT SYSDATE, TO_CHAR(SYSDATE,'HH') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'HH24') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'MI') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'SS') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;

----------- Date format Element Suffixes -------TH OR SP
SELECT SYSDATE, TO_CHAR(SYSDATE,'DDSPTH MONTH YYYYSP') FROM DUAL;

------------- Conversion Functions ------------- TO NUMBER ----
SELECT '$10,000.00',TO_NUMBER('$10,000.00','L99,999.99') FROM DUAL;
SELECT '$10,000.00',TO_NUMBER('$10,000.00','L99,999.99')+500 FROM DUAL;

------------- Conversion Functions ------------- TO DATE ----
SELECT SYSDATE, ROUND('19-OCT-11','MONTH') FROM DUAL;
SELECT SYSDATE, ROUND(TO_DATE('19-OCT-11'),'MONTH') FROM DUAL;
SELECT '12-AUGUST-2007','12-AUGUST-2007'+3 FROM DUAL;
SELECT '12-AUGUST-2007',TO_DATE('12-AUGUST-2007','DD-MONTH-YYYY')+3 FROM DUAL;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect