add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, August 22

Oracle SQL Functions



Function
Description
Demonstration
ABS
To get absolute value of any number.
SQL > select abs(-10) from dual;

  ABS(-10)
----------
        10
ADD_MONTHS
Add number of months is the specified date and get the next date.
SQL > select sysdate, add_months(sysdate, 2) from dual;

SYSDATE   ADD_MONTH
---------      ---------
10-APR-06 10-JUN-06
ASCII
This will return the ASCII value of the character specified.
SQL > select ascii('A') from dual;

ASCII('A')
----------
        65
AVG
To get the average
SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429
CEIL
To get the next greater integer.
SQL> select ceil(23.8) from dual;

CEIL(23.8)
----------
        24
CHR
It is reverse of ASCII function explained above, used to convert ASCII to its character.
SQL> select chr(65) from dual;

C
-
A
COALESCE


COMPOSE


CONCAT
To concatenate two strings. You can use two different table fields to concatenate.
SQL> select concat('Pak','istan') from dual;

CONCAT('
--------
Pakistan
CONVERT
To convert from one character set to another.
SQL> SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'WE8ISO8859P1', 'US7ASCII')
  2     FROM DUAL;

CONVERT('ÄÊÍÕØABCDE','WE8
-------------------------
¿¿ ¿¿ ¿¿ ¿¿ ¿¿ A B C D E

SQL>
COUNT
Count number of records in a table.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14
SQL> select deptno, count(*) from emp group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6
CURRENT_DATE
Returns date as per session time zone.
SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-APR-2006 11:04:23
DBTIMEZONE


DECODE
Decode is very useful function and is equivalent to if..elsif. The maximum no of components including expr, searches, results and default is 255.
SQL> select deptno, decode(deptno, 20, 'Dept code is 20', 30, 'Dept code is 30', 'Other') from emp;

    DEPTNO DECODE(DEPTNO,2
---------- ---------------
        20 Dept code is 20
        30 Dept code is 30
        30 Dept code is 30
        20 Dept code is 20
        30 Dept code is 30
        30 Dept code is 30
        10 Other
        20 Dept code is 20
        10 Other
        30 Dept code is 30
        20 Dept code is 20
        30 Dept code is 30
        20 Dept code is 20
        10 Other

14 rows selected.
FLOOR
Reverse of CEIL
SQL> select floor(23.7) from dual;

FLOOR(23.7)
-----------
         23
GREATEST
It will return the greatest string or number from the specified list.
SQL> select greatest('A','C','B') from dual;

G
-
C

SQL> select greatest(1,3,2) from dual;

GREATEST(1,3,2)
---------------
              3
INITCAP
It will caps the first character.
SQL> select initcap('sikandar hayat') from dual;

INITCAP('SIKAN
--------------
Sikandar Hayat
INSTR
Very useful function specially while working with strings, it will return the position of string in another specified string.
SQL> select instr('Pakistan','i') from dual;

INSTR('PAKISTAN','I')
---------------------
                    4
LAST_DAY
It will return last day of month of specified date.
SQL>  select last_day(to_date('15/02/2006')), last_day(sysdate) from dual;

LAST_DAY(T LAST_DAY(S
---------- ----------
28/02/2006 30/04/2006
LAST_VALUE


LEAD


LEAST


LENGTH
It will provide the length of string.
SQL> select length('Pakistan') from dual;

LENGTH('PAKISTAN')
------------------
                 8
LOWER
To convert upper case letters to lower.
SQL> select lower('USA') from dual;

LOW
---
Usa
LPAD
It will left pad the specified character within the length specified.
SQL> select lpad('Islamabad', 12, '*') from dual;

LPAD('ISLAMA
------------
***Islamabad

SQL> select rpad('123456', 12, '0') from dual;

LPAD('123456
------------
000000123456
LTRIM
This function is used to remove character from left side of string specified.
SQL> select ltrim('000123','0') from dual;

LTR
---
123
MAX
It will give maximum number.
SQL> select max(sal) from emp;

  MAX(SAL)
----------
      5000
MIN
Reverse of MAX as it will give minimum number.
SQL> select min(sal) from emp;

  MIN(SAL)
----------
       800
MOD
It will return remainder after dividing first number with 2nd.
SQL> select mod(3,2) from dual;

  MOD(3,2)
----------
         1

SQL> select mod(55,10) from dual;

MOD(55,10)
----------
         5
MONTHS_BETWEEN
To get number of months between two dates.
SQL> select months_between(to_date('01/03/2007'), to_date('01/01/2007')) Months from dual;

    MONTHS
----------
         2
NEXT_DAY
To get the next date of day specified. Like in example the it is Wednesday 12/04/2006 and in second query I got the date when next Wednesday will come.
SQL> select to_char(sysdate, 'day dd/mm/yyyy') from dual;

TO_CHAR(SYSDATE,'DAYDD/MM/YYYY')
--------------------------------------
wednesday 12/04/2006

SQL> select next_day(sysdate,'wednesday') from dual;

NEXT_DAY(
---------
19-APR-06
NVL
To replace a NULL value with a string. As in example NULL values are replaced with 0.
SQL> select empno, nvl(comm,0) from emp where deptno = 30;

     EMPNO NVL(COMM,0)
---------- -----------
      7499         300
      7521         500
      7654        1400
      7698           0
      7844           0
      7900           0
POWER
It will return m raise to the power nth. 32
SQL> select power(3,2) from dual;

POWER(3,2)
----------
         9
REPLACE
It will replace a string within a string.
SQL> select replace('AB D',' ','C') from dual;

REPL
----
ABCD

SQL> select replace('ABC','B','E') from dual;

REP
---
AEC
ROUND (number)
This function is used to round a number integer places on right side of decimal point. If no integer is specified then default is rounding to 0 places.
SQL> select round(25.529,2) from dual;

ROUND(25.529,2)
---------------
          25.53

SQL> select round(25.529) from dual;

ROUND(25.529)
-------------
           26
ROUND (date)
Rounding of date without format it will be rounded to nearest day.
SQL> select round(sysdate) from dual;

ROUND(SYSD
----------
15/04/2006

SQL> select round(sysdate, 'YEAR') from dual;

ROUND(SYSD
----------
01/01/2006

SQL> select round(sysdate, 'MONTH') from dual;

ROUND(SYSD
----------
01/04/2006
RPAD
It will right pad the specified character within the length specified. It is reverse of LPAD described above.
SQL> select rpad('Islamabad', 12, '*') from dual;

RPAD('ISLAMA
------------
Islamabad***

SQL> select rpad('123456', 12, '0') from dual;

RPAD('123456
------------
123456000000
RTRIM
It will trim the specified character(s) from the right side of string. Without specified of any characters it will remove spaces from the right if any.
SQL> select rtrim('USA', 'SA') from dual;

R
-
U

SQL> select length(rtrim('abc    ')) from dual;

LENGTH(RTRIM('ABC'))
--------------------
                   3
SOUNDEX
A very good function to find spelling differences. As in the example I have spelled name by differing ‘e’ and ‘a’ so both names are different and phonetic are same.
SQL> create table t(name varchar2(20));

Table created.

SQL> insert into t values ('Sikandar');

1 row created.

SQL> insert into t values ('Sikander');

1 row created.

SQL> select * from t;

NAME
--------------------
Sikandar
Sikander

SQL> select * from t where soundex(name) = soundex('Sikandar');

NAME
--------------------
Sikandar
Sikander

SQL>
SQRT
To get square root of a number.
SQL> select sqrt(49) from dual;

  SQRT(49)
----------
         7
SUBSTR
A string function called as substring, to get a portion of string from the position you specify up to length provided. As in example www.erpstuff.com is a string then 5 is starting position and 8 is number of characters. To search from right to left you will have to specify “-“.
SQL> select substr('www.erpstuff.com',5,8) from dual;

SUBSTR('
--------
erpstuff

SQL> select substr('www.erpstuff.com',-12,8) from dual;

SUBSTR('
--------
erpstuff
SUM
It is used to get sum of values.
SQL> select sum(sal) from emp;

  SUM(SAL)
----------
     29025
SYSDATE
It will return current system date and time.
SQL> select sysdate from dual;

SYSDATE
---------
18-APR-06
TO_CHAR (character)
To convert NCHAR, NVARCHAR2, CLOB OR NCLOB data to the database character set
SQL> select to_char('01110') from dual;

TO_CH
-----
01110
TO_CHAR (datetime)
You can use this function to covert date of (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE OR TIMESTAMP WITH LOCAL TIME ZONE) data types to VARCHAR2.
SQL> select to_char('15/04/2006') from dual;

TO_CHAR('1
----------
15/04/2006

SQL> select to_char(sysdate, 'dd/mm/yyyy HH:MI') from dual;

TO_CHAR(SYSDATE,
----------------
14/04/2006 03:24

SQL> select to_char(sysdate, 'dd/mm/yyyy HH24:MI') from dual;

TO_CHAR(SYSDATE,
----------------
14/04/2006 15:25
TO_CHAR (number)
To convert a NUMBER data type to VARCHAR2 data type.
SQL> select to_char(25) from dual;

TO
--
25
TO_CLOB


TO_DATE
To convert a date text string to date. You may also format the output.
SQL> select to_date('15/04/2006', 'dd/mm/yyyy') from dual;

TO_DATE('1
----------
15/04/2006
TO_NUMBER
This function is useful to convert a numeric string to NUMBER.
SQL> select to_number('0123') from dual;

TO_NUMBER('0123')
-----------------
              123
TRIM
To remove leading or trailing characters from a string.
SQL> select trim(0 from 0000001230000000) from dual;

TRI
---
123
UPPER
To change the case from lower to upper.
SQL> select upper('islamabad') from dual;

UPPER('IS
---------
ISLAMABAD
USER
It will return the current session logged in user.
SQL> select user from dual;

USER
------------------------------
SCOTT
Posted by Jithendra Kumar B
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)