Built In Function in Oracle

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

1. Numerical Functions
2. Character Functions
3. Date and Time Functions
4. Conversion Functions
5. 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 ; à 200
Select abs(-20*30) from Dual ; à 600

1. Exp (Exponential) :

This Function is used to find the exponential value for the given number. Ie “e” to the power of “x“ value ( e^x). where ‘e’ has a constant value ie 2.7182
Syntax : Exp(Numeric Expression)
Example : Select exp(0) from Dual ; à 1
Select exp(1) from Dual ; à 2.7182
Select exp(2) from Dual ; à 7.3890

1. 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 ; à 1.4142
select sqrt(16) from dual ; à 4
select sqrt(sal) from emp;
select sqrt(4*sqrt(2)) from dual;

1. 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 ; à 8
select power(5,3) from dual ; à 125
select power(sal,2) from emp;

1. 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(98.52) from dual; à 99
Select round(65.567,1) from dual ; à 65.6
Select round(65.567,2) from dual ; à 65.57

1. Ceil :This function returns the nearest integer greater than the given numeric expression

Syntax : Ceil(Numeric Expression)
Example : Select ceil(-23.567) from dual ; à -23
Select ceil(23.567) from dual ; à 24

1. Floor :This function returns the nearest integer smaller than the given numeric expression

Syntax : Floor(Numeric Expression)
Example : Select floor(-23.567) from dual ; à -24
Select floor(23.567) from dual ; à 23

1. 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; à 1
select log(2,10) from dual; à 3.3219
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;

1. 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 ; à 97
select Ascii(‘A’) from dual ; à 65

1. Chr :

This Function Returns The Ascii Character For The Given Ascii Value
Syntax : Chr(Ascii Value)
Example : select chr(65) from dual ; à A
Select chr(97) from dual ; à a

1. Length :

This Function Is Used To Find The Length Of The Given Character Expression
Syntax : Length ( character expression )
Example : select length (‘sairam’) from dual; à 6
select length (ename) from emp;

1. Upper :

This Function Is Used To Convert All Characters In To Upper Case
Syntax : Upper (Character Expression)
Example : select upper (‘sairam’) from dual; à SAIRAM

1. Lower :

This Function Is Used To Convert All Characters In To Lower Case
Syntax : Lower (Character Expression)
Example : select lower (‘SAIRAM’) from dual; à sairam
7. Ltrim :
This Function Removes Any Spaces From The Left Side of The String
Syntax : Ltrim(String)
Example : select ' sairam' from dual; --> sairam
select Ltrim(' sairam') from dual; --> sairam
8. Rtrim :
This Function Removes Any Space From The Right Side of The String
Syntax : Rtrim(String)
Example : select 'sairam ' from dual; --> sairam
select Rtrim('sairam ') from dual; --> sairam
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 ' sairam ' from dual; --> sairam
select Trim(' sairam ') from dual; --> sairam
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; --> operating
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
Example : select lpad(sal,10,'Rs. ') from emp;
output
Rs. Rs. 800
Rs. 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.
Example : select Rpad(sal,10,'Rs. ') from emp;
output
800Rs. Rs.
1200Rs. Rs
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(ename) from emp;
select Initcap(“WELCOME TO ALL”) from emp; à Welcome To All
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; à back
select Translate ('back and bill','b','j') from dual; è jack and jill
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;
Output
SYSDATE LAST_DAY(
----------- --------------
14-FEB-05 28-FEB-05
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;
Output
SYSDATE NEXT_DAY(
--------- ----------------
14-FEB-05 21-FEB-05
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;
MONTHS_BETWEEN(SYSDATE,TO_DATE('20-OCT-05','DD-MON-YY'))
-------------------------------------------------------------------------------
-2.432654
To eliminate decimal points, give the following form
select round(months_between(sysdate,
to_date('20-oct-05','dd-mon-yy'))) from dual
ROUND(MONTHS_BETWEEN(SYSDATE,TO_DA
----------------------------------------------------
-2
select round(months_between(to_date('20-oct-05','dd-mon-yy'),sysdate))
from dual;
ROUND(MONTHS_BETWEEN(TO_DAT
--------------------------------------------
2
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;
SYSDATE TO_CHAR(S
--------- ---------
15-FEB-05 15 02 05
select sysdate,to_char(sysdate,'month dd day w') from dual;
SYSDATE TO_CHAR(SYSDATE,'MONTHDD
--------- ----------------------------------------
15-FEB-05 february 15 tuesday 3
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;
GREATEST(10,20,40)
------------------
40
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;
LEAST(10,20,40)
------------------
10