Built in Function in Oracle
SQL
Functions are of two types,
they are
1. Single Rows
Functions : These functions will have effect on a single row of
the table
2. Group
Functions : These functions will have effect on a group of row's
Single Rows
Functions
- Numerical Functions
- Character Functions
- Date and Time Functions
- Conversion Functions
- General Functions or
Miscellaneous Functions
Numerical Functions
1. Abs :This function is used to convert any negative expression
into positive
Syntax : Abs
(Numeric Expression)
Example
: Select abs(-100) from Dual ; à 100
Select abs(300-500)
from Dual ;
Select abs(-20*30)
from Dual ;
2. Exp (Exponential) :
This Function is
used to find the exponential value for the given number. Ie “e” to the power of
“x“ value ( ex). where ‘e’ has a constant value ie 2.7182
Syntax :
Exp(Numeric Expression)
Example
: Select exp(0) from Dual ;
3. Sqrt (Square Root) :This
function is used to find the square root value for the given number
Syntax : Sqrt (
Number )
Example
: select sqrt(2) from dual ;
4. Power :This
function is used to find the power value ie x to the power of y value
Syntax : Power(x,y)
Example : select
power(2,3) from dual ;
5. Round :This
function is used to round off the given numeric expression according to
specified length or
precision
Syntax :
Round(Numeric Expression, Length)
Example
: Select round(65.567,2) from dual ;
6. Ceil :This
function returns the nearest integer greater than the given numeric expression
Syntax :
Ceil(Numeric Expression)
Example
: Select ceil(-23.567) from dual ;
7. Floor :This
function returns the nearest integer smaller than the given numeric expression
Syntax :
Floor(Numeric Expression)
Example
: Select floor(-23.567) from dual ;
8. Log : this
function is used to find the logarithm value for the given number and for the
given base
Syntax :
Log(Number, Base Value)
Example
: select log(10,10) from dual;
Character Functions or
Text Functions or String Functions
1. || or Concat :
Glues or concatenates
two strings together. The ‘|’ symbol is called as vertical
bar or pipe
Syntax : string1 ||
string2 ( for || Function)
Syntax :
Concat(string1 , string2) ( for concat Function)
Example
: select concat ( city, country) from location;
is same as select
city || country from location;
2. ASCII :
This Function
Returns The Ascii Code Value Of The Left Most Character From The Given
Character Expression
Syntax :
Ascii(Character Expression)
Example
: select Ascii(‘a’) from dual ;
3. Chr :
This Function
Returns The Ascii Character For The Given Ascii Value
Syntax : Chr(Ascii
Value)
Example
: select chr(65) from dual ;
4. Length :
This Function Is
Used To Find The Length Of The Given Character Expression
Syntax : Length (
character expression )
Example
: select length (‘Ritesh) from dual;
5. Upper :
This Function Is
Used To Convert All Characters In To Upper Case
Syntax
: Upper (Character Expression)
Example : select upper (‘Ritesh) from dual;
6. Lower :
This Function Is
Used To Convert All Characters In To Lower Case
Syntax
: Lower (Character Expression)
Example : select lower (‘Ritesh) from dual;
7. Ltrim
:
This Function
Removes Any Spaces From The Left Side of The String
Syntax
: Ltrim(String)
Example : select Ltrim(' Ritesh) from dual;
8. Rtrim :
This Function
Removes Any Space From The Right Side of The String
Syntax :
Rtrim(String)
Example : select
Rtrim(Ritesh ') from dual;
9. Trim :
(Oracle 9i)
If You Are Trimming
The Exact Same Data From Both The Beginning And Then End Of The String, Then
You Can Use The Trim Function In Place Of An Ltrim/Rtrim Combination
Syntax :
Trim(String)
Example: select
Trim(' Ritesh ') from dual;
10. Substr :
(Sub String)
This function
returns a part of the string from the specified Position to the specified
number of characters
Syntax:
Substr(String, Start Postion,Number of Characters)
Example
: select substr('disk operating system',6,9) from dual;
11. Lpad :
This function is
used to append the given text to the left side of any
column or String or
lpad function allows you to “pad” the left side of a column
with any set of
Characters.
Syntax : Lpad
(<Expression>,<Size>,<String Expression>)
Example
: select lpad(sal,7,'Rs. ') from emp;
output
Rs. 800
Rs.1200
12. Rpad :
This function is
used to append the given text to the right side of any column or string or lpad
function allows you to “pad” the left side of a column with any set of
characters.
Syntax : Rpad(<Expression>,<Size>,<String
Expression>)
Example
: select Rpad(sal,7,'Rs. ') from emp;
output
800Rs.
1200Rs.
13. Initcap :
This function takes
the initial letter of every word in a string or column and converts just those
letters to upper case.
Syntax : initcap
(String)
Example : select
Initcap(“WELCOME TO ALL”) from emp;
14. Translate
:
This function is
used to translate the source expression into target
expression that is present in the main string
Syntax :
Translate(Main String, Source Expression, Target Expression)
Example
: select Translate ('jack','j','b') from dual;
select Translate
('back and bill','b','j') from dual;
it will translate
only one char
Date and Time
Functions
1. Sysdate
: This Function Returns The System Date And Time. By default it
will show only the data but not the time. To show time also, we have to
user To_Char conversion function
Syntax : Sysdate
Example
: select sysdate from dual;
Output
SYSDATE
14-FEB-05
2. Add_Months
: This function is used to add the number of months to the months
part of the accepted dates. ( we can give positive/negative values )
Syntax :
Add_Months(Date, Number)
Example
: select sysdate, add_months(sysdate,5) from dual;
Output
SYSDATE ADD_MONTH
------------
---------------
14-FEB-05 14-JUL-05
3. Last_day
: This Function Is Used To Return The Last Day Of Accepted Date
(0r) Last Day Of the Month
Syntax : Last_day (
date expression )
Example
: select sysdate, Last_Day(sysdate) from dual;
4. Next_Day
: This function is used to find the Next day of the given weekday
name
Syntax :
Next_Day(Date Expression, Week day name)
Example
: select sysdate, next_day(sysdate, 'Monday') from dual;
5. months_between
This function is
used to find number of months between the given two dates
Syntax :
months_between(date expression1, date expression2)
Example : 1
select
months_between(sysdate,
to_date('20-oct-05','dd-mon-yy'))
from dual;
To eliminate
decimal points, give the following form
select
round(months_between(sysdate,
to_date('20-oct-05','dd-mon-yy')))
from dual
select
round(months_between(to_date('20-oct-05','dd-mon-yy'),sysdate))
from dual;
Conversion Functions
1. Nvl : It Is
Used To Convert The Null Values Of A Column Into Expression Or Value.
Syntax :
NVL(<Column>, <expression>)
Example
: select empno, ename, sal, comm, sal+comm from employee;
Using NVL Functions
select empno,
ename, sal, comm, sal+nvl(comm,0) from employee;
2. To_char
: This function is used to change the format of accepted date into
any predefind format.
Syntax :
To_char(<Date Expression>, <Format>)
Predefined Format’s
Are
mm/dd/yy
yy.mm.dd
dd/mm/yy
dd.mm.yy
dd-mm-yy
mon yy
mon dd, yy
hh:mm:ss
mon dd yyyy
hh:mi:mm (Am or Pm)
mm-dd-yy
yy mm dd
dd mon yyyy
hh:mi:ss:mm (24 hour format)
hh:mi:ss:mmm (24
hour)
dy (to find week
day number)
day (week day name)
dd (number of days
in month)
yyyy (year in four
digits)
yy (year of Last
two digits)
year (spelt in
terms of words)
month (month name)
w (week number)
Example : 1
select
sysdate,to_char(sysdate,'dd mm yy') from dual;
3. To_Date : this
function is used to convert any character expression into a date expression
according to the format you specified
Syntax : To_Date(<date
expression>, <format>)
Example :
insert into
student(jdate) values('jan-10-05')
ERROR at line 1:
ORA-01858: a
non-numeric character was found where a numeric was expected
insert into
student(jdate) values(to_date('jan-10-05','mon-dd-yy'));
JDATE
---------
10-JAN-05
General Functions or
Miscellaneous functions
1. show user
: This function is used to show the current user name
syntax : show
user;
Example : show
user;
Output : user
is "SCOTT"
2. uid : this
function is used to show the user id of the currently active user
syntax : uid
Example
: select uid from dual;
UID
----
18
3. Greatest : this
function is used to find the maximum value from a given list of values
Syntax :
Greatest(Value1,Value2,-----)
Example :
select
greatest(10,20,40) from dual;
4. Least : this
function is used to find the minimum value from a given list of values
Syntax :
Least(value1,value2,-----)
Example :
select
least(10,20,40) from dual;
1. || or Concat :
No comments:
Post a Comment