Aug 5, 2010

SQL Functions - Oracle

SQL Functions












Types of Functions






















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 
















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

No comments:

Post a Comment