Functions in Oracle:
A function is a program which accepts some input, perform some tasks and returns a value.
1. Single row functions
2. Multiple row functions
1. Single row functions :
These functions will process one row and returns one value per row.
- They are categorized into following categories:
(i) String
(ii) Date
(iii) Mathematical
(iv) Conversion
(v) Special
(vi) OLAP or Analytical
String Functions:
- UPPER( String )
- LOWER( String )
- INITCAP( String )
- LENGTH( String )
- SUBSTRING( String, start [,length] )
- INSTR( String1, String2 [,start,occurances] )
- LTRIM( String1 [,String2] )
- RTRIM( String1 [,String2] )
- TRIM( [Leading/Trailing/BOTH String2 from] String )
- RPAD( String, length, char )
- LPAD( String, length, char )
- REPLACE( String, searchString, replaceString )
- TRANSLATE( String, searchString, translateString )
- SOUNDEX( String )
- CONCAT( String1, String2 )
Date Functions:
- EXTRACT( year/month/day from date )
- ADD_MONTHS( date, no )
- LAST_DAY( date )
- NEXT_DAY( date, day )
- MONTHS_BETWEEN( date1, date2 )
Mathematical Functions:
- ABS( number )
- SIGN( number )
- POWER( number1, number2 )
- SQRT( number )
- MOD( number1, number2 )
- ROUND( number [,decimal_places] )
- TRUNC( number [,decimal_places] )
- CEIL( number )
- FLOOR( number )
1. UPPER( ):
Converts string to Uppercase.
Syntax: UPPER(string);
ex: Select upper(ename) from emp; -- returns all employee names in upper case.
Usage: Mostly used when there is a case in-sensitivity comparision.
ex: select * from emp where upper(ename) = 'SCOTT'; -- selects the SCOTT record without case sensitivity
2. LOWER( ):
Converts string to Lowercase.
Syntax: LOWER(string);
ex: Select lower(ename) from emp; -- returns all employee names in lower case.
Usage: Mostly used when there is a case in-sensitivity comparision.
ex: select * from emp where lower(ename) = 'adams'; -- selects the adams record without case sensitivity
3. INITCAP( )
Converts the Initial to capitals.
Syntax: INITCAP(string);
ex: Select INITCAP( "hello world..!!!" ) from dual; -- retuns Hello World..!!!
Usage: Mostly used when the name of the user is to be displayed.
ex: Select INITCAP( "hi shashi..!!!") from dual; -- retuns Hi Shashi..!!!
4. LENGTH( ):
Retuns the Length of the String.
Syntax: LENGTH(string);
ex: Select LENGTH("Ename") from EMP; -- returns 5
Usage: Mostly used in case of Checking the length of a password.
CREATE TABLE USER
(
UNAME VARCHAR2(20),
PWORD VARCHAR2(20) CHECK ( LENGTH(PWORD) > 8 )
);
5. SUBSTR( ):
Extract the part of the String.
Syntax: SUBSTR( str1, start [,length] );
-> start may be +ve/ -ve.
If +ve, starts from right side
If -ve, starts from left side
ex 1: Select SUBSTR( 'Hello', 2, 3) from dual; -- returns ell ( H=1, E=2; hence starting from 'e' 3 characters)
ex 2: Select SUBSTR( 'Hello', -2, 3) from dual; -- returns lo ( starting from right side; O=1, L=2; hence starting from 'L' 3 characters)
- no problem if there are in-sufficient characters.
6. INSTR( ):
Used to check the occurance of one string in another string.
Syntax: INSTR( str1, str2 [, start, occurance ] );
-> start may be +ve/ -ve.
If +ve, starts from right side
If -ve, starts from left side
-> If str1 contains str2, it returns the position.
else, it returns 0.
ex 1: Select INSTR( 'hello world', 'o') as "First Occurance",
INSTR( 'hello world','o',-1) as "Second Occurance",
INSTR( 'hello world','o',-1,2) as "First Occurance from Right Side"
from dual;
o/p: First Occurance: 5
Second Occurance: 8
First Occurance from Right Side: 5
Usage:
Extract Username from Email Id.
Ex: Display abc2xyz from 'abc2xyz@gmail.com'
-> Select substr('abc2xyz@gmail.com',1,instr( 'abc2xyz@gmail.com' , '@')-1) from dual;
Ex: Insert the data from CustomerS to CustomerT.
Rules: CNAME split into FNAME, MNAME, LNAME.
CustomerS CustomerT
- CID - CID
- CNAME - FNAME
- MNAME
- LNAME
-> Insert into CustomerT
Select cid, substr( cname, 1, instr( cname, ' ')-1)
, substr( cname, instr( cname, ' ')+1, instr( cname, ' ',1,2)-1)
, substr( cname, instr( cname, ' ',1,2)+1)
from CustomerS
7. LTRIM( ):
Used to trim spaces and un-wanted characters present on the left side of the String.
Syntax: LTRIM( string1 [, String2] );
ex: Select LTRIM (' hello') from dual; -- return hello.
Select LTRIM ('xxxhello','x') from dual; -- return hello.
8. RTRIM( ):
Used to trim spaces and un-wanted characters present on the right side of the String.
Syntax: RTRIM( string1 [, String2] );
ex: Select RTRIM ('hello ') from dual; -- return hello.
Select RTRIM ('helloxxx','x') from dual; -- return hello.
9. TRIM( ):
Used to trim on both sides. It trims spaces and un-wanted characters.
Syntax:
TRIM( ' String '); -- to trim both the sides.
TRIM( Leading 'String1' from <String2> );
TRIM( Trailing 'String1' from <String2> );
TRIM( BOTH 'String1' from <String2> );
ex: TRIM( ' Hello '); -- returns Hello
TRIM( Leading 'xyz' from 'xyzHelloxyz' ); -- returns Helloxyz
TRIM( Trailing 'xyz' from 'xyzHelloxyz' ); -- returns xyzHello
TRIM( BOTH 'xyz' from 'xyzHelloxyz' ); -- returns Hello
10. RPAD( ):
Used to fill the string with characters on Right side. It fills specified no of times.
Syntax: RPAD( String, length, char);
ex: Select RPAD( "Hello", 10, '*' ); -- returns Hello*****
Total no of characters = 10 (including String + *).
11. LPAD( ):
Used to fill the string with characters on Left side. It fills specified no of times.
Syntax: LPAD( String, length, char);
ex: Select LPAD( "Hello", 10, '*' ); -- returns *****Hello
Total no of characters = 10 (including String + *).
Usage:
Display ename, job and sal. Sal must be hidden such that 1 * for each thousand.
Select ename, job, RPAD( '*', sal/1000, '*') as 'Sal' from EMP;
12. REPLACE( ):
Used to replace the String with another String.
Syntax: replace( String, search, withString );
ex: Select replace ( 'UTI BANK', 'UTI', 'AXIS' ) from dual; -- returns AXIS BANK
Usage:
Display the names of the employees who has only one 'A' in his name.
Select ename from emp where length( replace (UPPER(ename), 'A',''))= length(ename)-1;
13. TRANSLATE( ):
Used to translate one character to another character.
Syntax: Translate( String, search, translateString );
-> Replace function is to replace String - String.
-> Translate function is to translate char - char.
ex: Select replace( 'Hello', 'ell', 'xyz' ) from dual; -- returns Hxyzo
Select translate( 'Hello', 'ell', 'xyz' ) from dual; -- returns Hxyyo
Here l->y , hence z is not assigned to any character.
Usage: Mostly used for Encoding and Decoding.
Encode Employee sal.
Select ename, translate( sal, '0123456789', 'abcdefghij' ) from emp;
14. SOUNDEX( ):
Used to find the string which sounds same.
Syntax: SOUNDEX( string );
ex: Select * from emp where SOUNDEX( ename ) = SOUNDEX( 'smyth' ); -- returns smith record.
Usage:
-> Display the employees with-out using LOWER and UPPER functions.
-> Mostly used to display results when there are any spelling mistakes.
15. CONCAT( ):
Used to cooncatenate two strings.
Syntax: CONCAT( string1, string2 );
ex: Select CONCAT( 'Hello', 'Welcome' ) from dual; -- returns 'Hello', 'Welcome'
-> We can also used '||' for concatenation.
Usage:
Display employee records as follows:
" Smith working as Clerk for deptno 10 ".
Select ename ||" working as "|| job ||" for deptno"|| deptno from emp;
Date Functions:
Date Operations:
1. Sysdate + 10 - 10 days are added to the system date.
2. Sysdate - 10 - 10 days are subtracted from the systemdate.
3. D1-D2 - Returns the difference in the Days.
4. D1+D2 - Invalid Operations.
Functions:
1. EXTRACT( )
2. ADD_MONTHS( )
3. LAST_DAY( )
4. NEXT_DAY( )
5. MONTHS_BETWEEN( )
1. Extract( ):
Used to extract part of the date.
Syntax: EXTRACT ( year/month/day from <date>);
ex 1: Select extract( year from sysdate ) from dual; -- returns current year
ex 2: Select extract( month from sysdate ) from dual; -- returns current month
ex 3: Select extract( day from sysdate ) from dual; -- returns current day
Usage:
Display employee records joined in year 2010, 2011.
Select * from emp where extract( year from hiredate ) in (2010, 2011);
2. ADD_MONTHS( ):
Used to add no of months to a date.
Syntax: ADD_MONTHS( date, no );
-> If no>0, months are added.
-. If no<0, months are sutracted.
ex 1: Select sysdate, ADD_MONTHS( sysdate, 2 ) as "Next date" from dual; -- returns '15-DEC-2011' as Sysdate, '15-FEB-2012' as "Next date"
ex 2: Select sysdate, ADD_MONTHS( sysdate, -2 ) as "Next date" from dual; -- returns '15-DEC-2011' as Sysdate, '15-OCT-2011' as "Next date"
Usage:
Every emp of the org has to retired at a stage of 40 yrs of exp.
Display all the employees who gets retired in 2015;
Select * from emp where EXTRACT( year from ADD_MONTHS(hiredate,40*12) ) = 2015;
3. LAST_DAY( ):
Used to get the last day of the month.
Syntax: LAST_DAY( date )
ex 1: Select LAST_DAY(sysdate) from dual; -- returns '31-DEC-11'
Usage:
What is current months 1st day .
Select ADD_MONTHS( LAST_DAY( sysdate) + 1, -1) from dual;
4. NEXT_DAY( ):
Used to get the next specified date starting from today.
Syntax: NEXT_DAY( date, day )
ex 1: select NEXT_DAY( sysdate, 'saturday' ) from dual; -- returns '17-DEC-11', where sysdate = '15-DEC-11'
Usage:
Display first monday of the month.
Select NEXT_DAY( LAST_DAY(ADD_MONTHS(sysdate,-1)), 'saturday' ) from dual;
5. MONTHS_BETWEEN( ):
Returns the no of months between 2 days.
Syntax: MONTHS_BETWEEN( date1, date2 );
ex 1: Select MONTHS_BETWEEN('01-JAN-2011', '21-MAR-2011') from dual; -- returns -2.6451613
Usage:
Display the employees with their experience.
Select ename, MONTHS_BETWEEN( sysdate, hiredate)/12 as "Experience" from EMP;
Mathematical Functions:
1. ABS( )
2. SIGN( )
3. POWER( )
4. SQRT( )
5. MOD( )
6. ROUND( )
7. TRUNC( )
8. CEIL( )
9. FLOOR( )
1. ABS( )
Returns the absolute values.
Syntax: ABS( number );
Ex 1: Select ABS( -10 ) from dual; -- returns 10
2. SIGN( )
Return the sign of the values.
Syntax: SIGN( number );
-> If number>0 , sign = 1
-> If number<0 , sign = -1
-> If number=0 , sign = 0
Ex 1: Select SIGN( 10 ) from dual; -- return 1
Select SIGN( -10 ) from dual; -- return -1
Select SIGN( 0 ) from dual; -- return 0
3. POWER( )
Return the power for the expression.
Syntax: POWER( number1, number2);
Ex 1: Select POWER( 3,2 ) from dual; -- returns 9.
4. SQRT( )
5. MOD( )
6. ROUND( )
7. TRUNC( )
8. CEIL( )
9. FLOOR( )
