.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

SQL Functions - Oracle

Posted By:Deepika Haridas       Posted Date: October 13, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

This article describes about the simple functions used in 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
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend