Built in Functions 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 asvertical 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 userTo_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)