SQL Functions - Oracle


This article describes about the simple functions used in Oracle

SQL Functions



SQL Functions - Oracle1


Types of Functions

SQL Functions - Oracle2


Single Row Functions -

1. Single row functions:
2. Manipulate data items
3. Accept arguments and return one value
4. Act on each row returned
5. Return one result per row
6. May modify the data type
7. Can be nested
8. Accept arguments which can be a column or an expression

SQL Functions - Oracle3

Case manipulation functions -

1. LOWER
(e.g. LOWER('SQL') -> sql)
2. UPPER
(e.g. UPPER('sql Course') -> SQL COURSE)
3. INITCAP
(e.g. INITCAP('sql') -> Sql)

Character manipulation functions -

1. CONCAT
(e.g. CONCAT('Hello', 'World') -> HelloWorld)
2. SUBSTR
(e.g. SUBSTR('HelloWorld',1,5) -> Hello)
3. LENGTH
(e.g. LENGTH('HelloWorld') -> 10)
4. INSTR
(e.g. INSTR('HelloWorld', 'W') -> 6)
5. LPAD | RPAD
(e.g. LPAD(salary,10,'*') -> *****24000)
(e.g RPAD(salary, 10, '*') -> 24000*****)
6. TRIM
(e.g. TRIM('H' FROM 'HelloWorld') -> elloWorld)
7. REPLACE

Number Functions -

1. ROUND
(e.g ROUND(45.926, 2) -> 45.93)
2. TRUNC
(e.g. TRUNC(45.926, 2) -> 45.92)
3. MOD
(e.g. MOD(1600, 300) -> 100)

Dates functions -

Conversion functions -

1. Implicit data type conversion
- From varchar2/char to number
- From varchar2/char to date
- From date to varchar2
- From number to varchar2

2. explicit data type conversion

- From varchar2/char to number using TO_NUMBER
- From varchar2/char to date using TO_DATE
- From date to varchar2 using TO_CHAR
- From number to varchar2 using TO_CHAR

Reference: http://angeldeeps.blogspot.com/2010/08/sql-functions-oracle.html


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: