SIGN FUN:
************
SYN:SIGN(N)--IF
N<0,RETURNS -1
-- IF N=0,RETURNS 0
-- IF N>0,RETURNS 1
SELECT SIGN(-10) FROM DUAL;
-1
SELECT SIGN(10) FROM DUAL;
1
SELECT SIGN(0) FROM DUAL;
0
SELECT SIGN(123.654) FROM DUAL;
1
SELECT ENAME,SAL,COMM,SIGN(SAL-COMM),ABS(SAL-COMM) FROM EMP
WHERE SIGN(SAL-COMM)=-1;
ENAME
SAL COMM SIGN(SAL-COMM) ABS(SAL-COMM)
MARTIN 1250
1400 -1 150
DATE FUNCTIONS:
******************
STSDATE:
***********
DATE+NUMBER
DATE-NUMBER
DATE-DATE
DATE+NUMBER/24
SELECT SYSDATE FROM DUAL;
1/15/2012 4:50:42 PM
SELECT SYSDATE,SYSDATE+10 FROM DUAL;
1/15/2012 4:54:18 PM 1/25/2012 4:54:18 PM
SELECT
SYSDATE,SYSDATE+48/24 FROM DUAL;
1/15/2012 4:56:39 PM 1/17/2012
4:56:39 PM
SELECT ENAME,HIREDATE,HIREDATE+10 FROM EMP;
ENAME HIREDATE HIREDATE+10
SMITH 12/17/1980 12/27/1980
ALLEN 2/20/1981 3/2/1981
WARD 2/22/1981 3/4/1981
JONES 4/2/1981 4/12/1981
MARTIN 9/28/1981 10/8/1981
BLAKE 5/1/1981 5/11/1981
CLARK 6/9/1981 6/19/1981
SCOTT 12/9/1982 12/19/1982
KING 11/17/1981 11/27/1981
TURNER 9/8/1981 9/18/1981
ADAMS 1/12/1983 1/22/1983
JAMES 12/3/1981 12/13/1981
FORD 12/3/1981 12/13/1981
MILLER 1/23/1982 2/2/1982
SELECT ENAME,HIREDATE,HIREDATE-5 FROM EMP;
ENAME HIREDATE HIREDATE-5
SMITH 12/17/1980 12/12/1980
ALLEN 2/20/1981 2/15/1981
WARD 2/22/1981 2/17/1981
JONES 4/2/1981 3/28/1981
MARTIN 9/28/1981 9/23/1981
BLAKE 5/1/1981 4/26/1981
CLARK 6/9/1981 6/4/1981
SCOTT 12/9/1982 12/4/1982
KING 11/17/1981 11/12/1981
TURNER 9/8/1981 9/3/1981
ADAMS 1/12/1983 1/7/1983
JAMES 12/3/1981 11/28/1981
FORD 12/3/1981 11/28/1981
MILLER 1/23/1982 1/18/1982
SELECT ENAME,HIREDATE,SYSDATE-HIREDATE"EX
OF EMPS" FROM EMP;
ENAME HIREDATE EXOFEMPS
SMITH 12/17/1980 11351.7084027778
ALLEN 2/20/1981 11286.7084027778
WARD 2/22/1981 11284.7084027778
JONES 4/2/1981 11245.7084027778
MARTIN 9/28/1981 11066.7084027778
BLAKE 5/1/1981 11216.7084027778
CLARK 6/9/1981 11177.7084027778
SCOTT 12/9/1982 10629.7084027778
KING 11/17/1981 11016.7084027778
TURNER 9/8/1981 11086.7084027778
ADAMS 1/12/1983 10595.7084027778
JAMES 12/3/1981 11000.7084027778
FORD 12/3/1981 11000.7084027778
MILLER 1/23/1982 10949.7084027778
SELECT ENAME,ROUND((SYSDATE-HIREDATE)/7) WEEKS
FROM EMP;
ENAME WEEKS
SMITH 1622
ALLEN 1612
WARD 1612
JONES 1607
MARTIN 1581
BLAKE 1602
CLARK 1597
SCOTT 1519
KING 1574
TURNER 1584
ADAMS 1514
JAMES 1572
FORD 1572
MILLER 1564
SELECT
EMPNO,HIREDATE,ROUND((SYSDATE-HIREDATE)/365) FROM EMP;
EMPNO HIREDATE ROUND((SYSDATE-HIREDATE)/365)
7369 12/17/1980 31
7499 2/20/1981 31
7521 2/22/1981 31
7566 4/2/1981 31
7654 9/28/1981 30
7698 5/1/1981 31
7782 6/9/1981 31
7788 12/9/1982 29
7839 11/17/1981 30
7844 9/8/1981 30
7876 1/12/1983 29
7900 12/3/1981 30
7902 12/3/1981 30
7934 1/23/1982 30
ADD_MONTHS FUN:
*****************
SYN:ADD_MONTHS(D,+(OR)-N)
SELECT SYSDATE,ADD_MONTHS(SYSDATE,1) FROM DUAL;
1/15/2012 5:46:17 PM 2/15/2012 5:46:17 PM
SELECT
ENAME,SAL,HIREDATE,ADD_MONTHS(HIREDATE,1) FROM EMP WHERE DEPTNO=30;
ENAME SAL HIREDATE ADD_MONTHS(HIREDATE,1)
ALLEN 1600 2/20/1981 3/20/1981
WARD 1250 2/22/1981 3/22/1981
MARTIN 1250 9/28/1981 10/28/1981
BLAKE 2850 5/1/1981 6/1/1981
TURNER 1500 9/8/1981 10/8/1981
JAMES 950 12/3/1981 1/3/1982
MONTH_BETWEEN
FUN:
*******************
SYN:
MONTHS_BETWEEN(D1,D2)
SELECT
ENAME,HIREDATE,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)"EXPERIENCE IN
YEARS" FROM EMP;
ENAME HIREDATE EXPERIENCE
IN YEARS
SMITH 12/17/1980 31
ALLEN 2/20/1981 31
WARD 2/22/1981 31
JONES 4/2/1981 31
MARTIN 9/28/1981 30
BLAKE 5/1/1981 31
CLARK 6/9/1981 31
SCOTT 12/9/1982 29
KING 11/17/1981 30
TURNER 9/8/1981 30
ADAMS 1/12/1983 29
JAMES 12/3/1981 30
FORD 12/3/1981 30
MILLER 1/23/1982 30
SELECT
ENAME,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE
MONTHS_BETWEEN(SYSDATE,HIREDATE)>320;
ENAME HIREDATE EXPERIENCE
IN YEARS
SMITH 12/17/1980 31
ALLEN 2/20/1981 31
WARD 2/22/1981 31
JONES 4/2/1981 31
MARTIN 9/28/1981 30
BLAKE 5/1/1981 31
CLARK 6/9/1981 31
SCOTT 12/9/1982 29
KING 11/17/1981 30
TURNER 9/8/1981 30
ADAMS 1/12/1983 29
JAMES 12/3/1981 30
FORD 12/3/1981 30
MILLER 1/23/1982 30
NEXT DAY FUN:
***************
SYN:NEXT_DAY(D,CHAR)
SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRI') FROM
DUAL;
1/15/2012 6:00:46 PM 1/20/2012 6:00:46 PM
SELECT SAL,HIREDATE,NEXT_DAY(HIREDATE,'MONDAY')
FROM EMP;
SAL HIREDATE NEXT_DAY(HIREDATE,'MONDAY')
800 12/17/1980 12/22/1980
1600 2/20/1981 2/23/1981
1250 2/22/1981 2/23/1981
2975 4/2/1981 4/6/1981
1250 9/28/1981 10/5/1981
2850 5/1/1981 5/4/1981
2450 6/9/1981 6/15/1981
3000 12/9/1982 12/13/1982
5000 11/17/1981 11/23/1981
1500 9/8/1981 9/14/1981
1100 1/12/1983 1/17/1983
950 12/3/1981 12/7/1981
3000 12/3/1981 12/7/1981
1300 1/23/1982 1/25/1982
LAST DAY FUN:
**************
SYN:LAST_DAY(D)
SELECT
SYSDATE,LAST_DAY(SYSDATE)LAST,LAST_DAY(SYSDATE)-SYSDATE DAYSLEFT FROM DUAL;
SYSDATE LAST DAYSLEFT
1/15/2012 6:06:17 PM 1/31/2012 16
REAL
TIME SENARIO:
*******************
SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM
DUAL;
1/1/2012 6:09:39 PM
ROUNDING OF DATES:
*******************
SYN:ROUND(DATE,'FORMAT')
SELECT ROUND(SYSDATE,'DAY') FROM DUAL;
1/15/2012
SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
1/1/2012
SELECT ROUND(SYSDATE,'YEAR')FROM DUAL;
1/1/2012
TRUNCATING DATES:
********************
SYN:TRUNC(DATE,'FORMAT')
SELECT ROUND(SYSDATE,'DAY'),TRUNC(SYSDATE,'DAY')
FROM DUAL;
ROUND(SYSDATE,'DAY') TRUNC(SYSDATE,'DAY')
1/15/2012 1/15/2012
SELECT
ROUND(SYSDATE,'MONTH'),TRUNC(SYSDATE,'MONTH') FROM DUAL;
1/1/2012
SELECT
ROUND(SYSDATE,'YEAR'),TRUNC(SYSDATE,'YEAR') FROM DUAL;
ROUND(SYSDATE,'YEAR') TRUNC(SYSDATE,'YEAR')
1/1/2012 1/1/2012
CONVERSION FUNCTIONS:
************************
2TYPE:
IMPLICIT
DATA TYPE CONVERSION:
EXPLICIT
DATA TYPE CONVERSION:
IMPLICIT DATATYPE CONVERSION:
*****************************
FROM TO
------ --------
VARCHAR2 OR CHAR NUMBER
VARCHAR2 OR CHAR DATE
NUMBER VARCHAR2
EXPLICIT DATA TYPE CONVERSION:
*******************************
TO_CHAR
TO_NUMBER
TO_DATE
TO_CHAR
CONVERSION:
------------------
TO_CHAR(NUMBER
CONVERSION)
TO_CHAR(DATE
CONVERSION)
TO_CHAR(NUMBER
CONVERSION):
***************************
SYN:TO_CHAR(NUMBER,FMT)
TO_CHAR(DATE
CONVERSION):
*************************
SYN:TO_CHAR(DATE,FMT)
DECIMAL
INDICATOR:D->99D99
**************************
SELECT 1234,TO_CHAR(1234,'9999D99') FROM DUAL;
1234 1234.00
SELECT 1234,TO_CHAR(1234,'999D99')FROM DUAL;
1234 #######
SELECT 123,TO_CHAR(123,'999D99')FROM DUAL;
123 123.00
SELECT 36,TO_CHAR(36,'99D9999')FROM DUAL;
36 36.0000
SELECT TO_CHAR(567,'99D9') FROM DUAL;
#####
SCIENTIFIC NOTATION
INDICATOR:EEEE->9.9EEEE
******************************
SELECT TO_CHAR(5634,'9.9EEEE') FROM DUAL;
5.6E+03
SELECT
TO_CHAR(2345,'99.9EEEE')FROM DUAL;
2.3E+03
SELECT
TO_CHAR(23456,'99.9EEEE')FROM DUAL;
2.3E+04
GROUP SEPARATION:G->9G999
***************************
SELECT TO_CHAR(1234567,'99G99G999')FROM DUAL;
12,34,567
SELECT
TO_CHAR(SAL,'9G99G999')FROM EMP;
TO_CHAR(SAL,'9G99G999')
800
1,600
1,250
2,975
1,250
2,850
2,450
3,000
5,000
1,500
1,100
950
3,000
1,300
SELECT TO_CHAR(805087,'9G99G999') FROM DUAL;
8,05,087
JULIAN DAY INDICATOR:J
***********************
SELECT SYSDATE,TO_CHAR(SYSDATE,'J')FROM DUAL;
1/16/2012 8:47:01 PM 2455943
SELECT
EMPNO,ENAME,HIREDATE,TO_CHAR(HIREDATE,'J-DDD-DD-D')FROM EMP;
EMPNO ENAME HIREDATE TO_CHAR(HIREDATE,'J-DDD-DD-D')
7369 SMITH 12/17/1980 2444591-352-17-4
7499 ALLEN 2/20/1981 2444656-051-20-6
7521 WARD 2/22/1981 2444658-053-22-1
7566 JONES 4/2/1981 2444697-092-02-5
7654 MARTIN 9/28/1981 2444876-271-28-2
7698 BLAKE 5/1/1981 2444726-121-01-6
7782 CLARK 6/9/1981 2444765-160-09-3
7788 SCOTT 12/9/1982 2445313-343-09-5
7839 KING 11/17/1981 2444926-321-17-3
7844 TURNER 9/8/1981 2444856-251-08-3
7876 ADAMS 1/12/1983 2445347-012-12-4
7900 JAMES 12/3/1981 2444942-337-03-5
7902 FORD 12/3/1981 2444942-337-03-5
7934 MILLER 1/23/1982 2444993-023-23-7
LOCAL CURRENCY
INDICATOR:L->L999 OR 999L
*******************************************
SELECT 1234,TO_CHAR(1234,'L9999') FROM DUAL;
1234 $1234
SELECT SAL,TO_CHAR(SAL,'L999999')CURRENCY FROM
EMP WHERE DEPTNO=10;
SAL CURRENCY
2450 $2450
5000 $5000
1300 $1300
SELECT
SAL,TO_CHAR(SAL,'L99G999D99','NLS_CURRENCY=INDRUPEES') SAL FROM EMP WHERE
DEPTNO=20;
SAL SAL_1
800 INDRUPEES800.00
2975 INDRUPEES2,975.00
3000 INDRUPEES3,000.00
1100 INDRUPEES1,100.00
3000 INDRUPEES3,000.00
TRAILING MINUS INDICATOR:MI->9999MI
************************************
SELECT
-10000,TO_CHAR(-10000,'L99G999D99MI')FROM DUAL;
-10000 $10,000.00-
SELECT SAL,COMM,COMM-SAL,TO_CHAR(COMM-SAL,'L99G999D99MI')
FROM EMP;
SAL COMM COMM-SAL TO_CHAR(COMM-SAL,'L99G999D99MI')
800
1600 300 -1300
$1,300.00-
1250 500 -750 $750.00-
2975
1250 1400 150 $150.00
2850
2450
3000
5000
1500 0 -1500
$1,500.00-
1100
950
3000
1300
NEGATIVE NUMBER INDICATOR:PR->9999PR
***************************************
SELECT TO_CHAR(-1000,'L99G999D99PR')FROM DUAL;
<$1,000.00>
SELECT
SAL,COMM,COMM-SAL,TO_CHAR(COMM-SAL,'L9999PR')FROM EMP;
SAL COMM COMM-SAL TO_CHAR(COMM-SAL,'L9999PR')
800
1600 300 -1300
<$1300>
1250 500 -750
<$750>
2975
1250 1400 150 $150
2850
2450
3000
5000
1500 0 -1500
<$1500>
1100
950
3000
1300
ROMAN NUMBER INDICATOR:
************************
SELECT 12,TO_CHAR(12,'RN')FROM
DUAL;
12 XII
SELECT 12,TO_CHAR(12,'rn')FROM
DUAL;
12 xii
SIGN INDICATOR:S->S99999 OR
99999S
**********************************
SELECT
1000,TO_CHAR(1000,'S9999')FROM DUAL;
1000 +1000
SELECT
1000,TO_CHAR(-1000,'S9999')FROM DUAL;
1000 -1000
SELECT TO_CHAR(1000,'9999S')FROM
DUAL;
1000+
SELECT
TO_CHAR(-1000,'999999999S')FROM DUAL;
1000-
SELECT
SAL,TO_CHAR(SAL,'S99999')FROM EMP;
SAL TO_CHAR(SAL,'S99999')
800 +800
1600 +1600
1250 +1250
2975 +2975
1250 +1250
2850 +2850
2450 +2450
3000 +3000
5000 +5000
1500 +1500
1100 +1100
950 +950
3000 +3000
1300 +1300
SELECT SAL,TO_CHAR(-SAL,'999999S')FROM EMP;
SAL TO_CHAR(-SAL,'999999S')
800 800-
1600 1600-
1250 1250-
2975 2975-
1250 1250-
2850 2850-
2450 2450-
3000 3000-
5000 5000-
1500 1500-
1100 1100-
950 950-
3000 3000-
1300 1300-
HEXADECIMAL INDICATOR:X->XXXX
*****************************
SELECT 2000,TO_CHAR(2000,'XXXXX')FROM DUAL;
2000 7D0
SELECT ENAME,SAL,TO_CHAR(SAL,'XXXX')HEXSAL FROM
EMP;
ENAME SAL HEXSAL
SMITH 800 320
ALLEN 1600 640
WARD 1250 4E2
JONES 2975 B9F
MARTIN 1250 4E2
BLAKE 2850 B22
CLARK 2450 992
SCOTT 3000 BB8
KING 5000 1388
TURNER 1500 5DC
ADAMS 1100 44C
JAMES 950 3B6
FORD 3000 BB8
MILLER 1300 514
GROUP SEPARATOR:->9,999
************************
SELECT 20000,TO_CHAR(20000,'99,999.999')FROM
DUAL;
20000 20,000.000
SELECT ENAME,SAL,TO_CHAR(SAL,'99,999.999')FROM
EMP;
ENAME SAL TO_CHAR(SAL,'99,999.999')
SMITH 800 800.000
ALLEN 1600 1,600.000
WARD 1250 1,250.000
JONES 2975 2,975.000
MARTIN 1250 1,250.000
BLAKE 2850 2,850.000
CLARK 2450 2,450.000
SCOTT 3000 3,000.000
KING 5000 5,000.000
TURNER 1500 1,500.000
ADAMS 1100 1,100.000
JAMES 950 950.000
FORD 3000 3,000.000
MILLER 1300 1,300.000
DECIMAL INDICATOR:->99.99
**************************
SELECT 20000,TO_CHAR(20000,'L99,999.99')FROM
DUAL;
20000 $20,000.00
SELECT ENAME,SAL,TO_CHAR(SAL,'L99,999.99')FROM
EMP;
ENAME SAL TO_CHAR(SAL,'L99,999.99')
SMITH 800 $800.00
ALLEN 1600 $1,600.00
WARD 1250 $1,250.00
JONES 2975 $2,975.00
MARTIN 1250 $1,250.00
BLAKE 2850 $2,850.00
CLARK 2450 $2,450.00
SCOTT 3000 $3,000.00
KING 5000 $5,000.00
TURNER 1500 $1,500.00
ADAMS 1100 $1,100.00
JAMES 950 $950.00
FORD 3000 $3,000.00
MILLER 1300 $1,300.00
DOLLAR INDICATOR:$->$9999
****************************
SELECT 20000,TO_CHAR(20000,'$99,999.99')FROM
DUAL;
20000 $20,000.00
ZERO INDICATOR:0->0999 OR 9990
*******************************
SELECT 1000,TO_CHAR(1000,'0999999')FROM DUAL;
1000 0001000
SELECT TO_CHAR(1000,'09999990')FROM DUAL;
00001000
DIGIT
PLACE MARKER:9->9999
****************************
SELECT 10000,600,TO_CHAR(10000-600,'99999')FROM
DUAL;
10000 600
9400
SELECT 10000,600,TO_CHAR(600-10000,'99999')FROM
DUAL;
10000 600
-9400
ISO CURRENCY INDICATOR:C->C9999
*********************************
SELECT 1000,TO_CHAR(1000,'C9999.99')FROM DUAL;
1000 USD1000.00
SELECT ENAME,SAL,TO_CHAR(SAL,'C9999.99')FROM
EMP;
ENAME SAL TO_CHAR(SAL,'C9999.99')
SMITH 800 USD800.00
ALLEN 1600 USD1600.00
WARD 1250 USD1250.00
JONES 2975 USD2975.00
MARTIN 1250 USD1250.00
BLAKE 2850 USD2850.00
CLARK 2450 USD2450.00
SCOTT 3000 USD3000.00
KING 5000 USD5000.00
TURNER 1500 USD1500.00
ADAMS 1100 USD1100.00
JAMES 950 USD950.00
FORD 3000 USD3000.00
MILLER 1300 USD1300.00
No comments:
Post a Comment