Numeric Functions
SQL> select
abs(-5),abs(0),abs(5) from dual;
ABS(-5)
ABS(0) ABS(5)
----------
---------- ----------
5 0 5
SQL> select
sign(-5),sign(0),sign(5) from dual;
SIGN(-5)
SIGN(0) SIGN(5)
----------
---------- ----------
-1 0 1
SQL> select
power(2,3),power(2.5,1.6) from dual;
POWER(2,3)
POWER(2.5,1.6)
----------
--------------
8
4.33215527
SQL> select
sqrt(25),sqrt(2) from dual;
SQRT(25)
SQRT(2)
----------
----------
5 1.41421356
SQL> select
sin(30),sin(30*3.14/180) from dual;
SIN(30) SIN(30*3.14/180)
----------
----------------
-.98803162 .499770103
SQL> select
asin(-0.9) from dual;
ASIN(-0.9)
----------
-1.1197695
SQL> select
asin(1),sinh(4) from dual;
ASIN(1)
SINH(4)
----------
----------
1.57079633
27.2899172
SQL> select
mod(3,4),mod(0,0) from dual;
MOD(3,4)
MOD(0,0)
----------
----------
3 0
SQL>
select remainder(3,4) from dual;
REMAINDER(3,4)
--------------
-1
SQL>
select remainder(13,4) from dual;
REMAINDER(13,4)
---------------
1
SQL> select
exp(3) from dual;
EXP(3)
----------
20.0855369
SQL> select
ln(2),log(10,100) from dual;
LN(2) LOG(10,100)
----------
-----------
.693147181 2
SQL> select
ceil(-5.6),ceil(0),ceil(5.6) from dual;
CEIL(-5.6) CEIL(0)
CEIL(5.6)
----------
---------- ----------
-5 0 6
SQL> select
floor(-5.6),floor(0),floor(5.6) from dual;
FLOOR(-5.6) FLOOR(0) FLOOR(5.6)
-----------
---------- ----------
-6 0 5
String Functions
SQL> select
length('hello') from dual;
LENGTH('HELLO')
---------------
5
SQL> select
lower('hello'),lower('HELLO'),lower('HeLLo') from dual;
LOWER LOWER LOWER
----- -----
-----
hello hello
hello
SQL> select
upper('hello'),upper('HELLO'),upper('HeLLo') from dual;
UPPER UPPER
UPPER
----- -----
-----
HELLO HELLO
HELLO
SQL> select
initcap('hello') from dual;
INITC
-----
Hello
SQL> select
reverse('hello') from dual;
REVER
-----
olleh
SQL> select
concat('hello','world') from dual;
CONCAT('HE
----------
helloworld
SQL> select
lpad('hello',10,'*') from dual;
LPAD('HELL
----------
*****hello
SQL> select
rpad('hello',10,'*') from dual;
RPAD('HELL
----------
hello*****
SQL>
select ltrim('****hello','*') from dual;
LTRIM
-----
hello
SQL>
select rtrim('hello****','*') from dual;
RTRIM
-----
hello
SQL> select
substr('helloworld',6) from dual;
SUBST
-----
world
SQL> select
substr('helloworld',3,5) from dual;
SUBST
-----
llowo
SQL> select
substr('helloworld',-3) from dual;
SUB
---
rld
SQL> select
substr('helloworld',-3,2) from dual;
SU
--
rl
SQL> select
substr('helloworld',-3,3) from dual;
SUB
---
rld
SQL> select
substr('helloworld',-3,4) from dual;
SUB
---
rld
SQL> select
instr('hello','l') from dual;
INSTR('HELLO','L')
------------------
3
SQL> select
instr('helloworld','l',5) from dual;
INSTR('HELLOWORLD','L',5)
-------------------------
9
SQL> select
instr('helloworld','l',-5) from dual;
INSTR('HELLOWORLD','L',-5)
--------------------------
4
SQL> select
replace('hello','h','x') from dual;
REPLA
-----
xello
SQL> select
replace('hello','he','xy') from dual;
REPLA
-----
xyllo
SQL> select
replace('hello','he','x') from dual;
REPL
----
xllo
SQL> select
replace('hello','hl','xy') from dual;
REPLA
-----
hello
SQL> select
translate('hello','hl','xy') from dual;
TRANS
-----
xeyyo
SQL> select
translate('hello','he','xy') from dual;
TRANS
-----
xyllo
SQL>
select ascii('A') from dual;
ASCII('A')
----------
65
SQL>
select chr(65) from dual;
C
-
A
SQL> select
ename from emp where soundex(ename)=soundex('SMITH') ;
ENAME
----------
SMITH
SQL> select
ename from emp where soundex(ename)=soundex('smith') ;
ENAME
----------
SMITH
SQL> select
vsize('hello'),vsize('h'),vsize(1) from dual;
VSIZE('HELLO')
VSIZE('H') VSIZE(1)
--------------
---------- ----------
5 1 2
Aggregate
Functions
SQL> select
count(*) ,count(empno),count(comm) from emp;
COUNT(*) COUNT(EMPNO) COUNT(COMM)
----------
------------ -----------
14 14 4
SQL> select
sum(sal),avg(sal),max(sal),min(sal) from emp;
SUM(SAL)
AVG(SAL) MAX(SAL) MIN(SAL)
----------
---------- ---------- ----------
29025 2073.21429 5000 800
SQL> select
variance(sal) from emp;
VARIANCE(SAL)
-------------
1398313.87
SQL> select
stddev(sal) from emp;
STDDEV(SAL)
-----------
1182.50322
Other Functions
SQL> select
least(10,0,-9,30) from dual;
LEAST(10,0,-9,30)
-----------------
-9
SQL> select
greatest(10,0,-9,30) from dual;
GREATEST(10,0,-9,30)
--------------------
30
SQL> select
trunc(5.6789),trunc(5.6789,3),trunc(5.6789,1) from dual;
TRUNC(5.6789)
TRUNC(5.6789,3) TRUNC(5.6789,1)
-------------
--------------- ---------------
5 5.678 5.6
SQL> select
round(5.6789),round(5.6789,3),round(5.6789,1) from dual;
ROUND(5.6789)
ROUND(5.6789,3) ROUND(5.6789,1)
-------------
--------------- ---------------
6 5.679 5.7
Date and
Functions
SQL> select
months_between('10-aug-12','15-sep-13') from dual;
MONTHS_BETWEEN('10-AUG-12','15-SEP-13')
---------------------------------------
-13.16129
SQL> select
add_months('10-aug-12',3) from dual;
ADD_MONTH
---------
10-NOV-12
SQL> select
next_day('10-aug-12','thu') from dual;
NEXT_DAY(
---------
16-AUG-12
SQL> select
last_day('10-aug-12') from dual;
LAST_DAY(
---------
31-AUG-12
SQL> SELECT
TO_CHAR(
2
ADD_MONTHS(sysdate,1),
3
'DD-MON-YYYY') from dual;
TO_CHAR(ADD_MONTHS(S
--------------------
11-SEP-2012
SQL> select
to_char(to_date('15-aug-12'),'dd-mon-yyyy') from dual;
TO_CHAR(TO_DATE('15-
--------------------
15-aug-2012
SQL> ALTER
SESSION SET TIME_ZONE = '-5:0';
Session altered.
SQL> ALTER
SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT
SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_DATE
--------------------
-05:00
11-AUG-2012
01:48:04
SQL> select
current_date from dual;
CURRENT_DATE
--------------------
11-AUG-2012
01:48:29
SQL> ALTER
SESSION SET TIME_ZONE = '-5:0';
Session altered.
SQL> ALTER
SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT
SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
-05:00
11-AUG-12
01.49.40.738000 AM -05:00
SQL>
select dbtimezone from dual;
DBTIME
------
+00:00
Conversion
Functions
SQL> select
to_number('123') from dual;
TO_NUMBER('123')
----------------
123
SQL> select
to_char(sysdate,'day month year') from dual;
TO_CHAR(SYSDATE,'DAYMONTHYEAR')
--------------------------------------------------------------------------------
friday august
twenty twelve
SQL> select
to_date('12-aug-12') from dual;
TO_DATE('
---------
12-AUG-12
SQL> select to_date('12/aug/12')
from dual;
TO_DATE('
---------
12-AUG-12