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( )
MMORPG OYUNLAR
ReplyDeleteInstagram Takipçi Satın Al
tiktok jeton hilesi
TİKTOK JETON HİLESİ
Saç ekim antalya
INSTAGRAM TAKİPÇİ SATİN AL
İNSTAGRAM TAKİPÇİ SATIN AL
Metin Pvp
INSTAGRAM TAKİPCİ SATİN AL
smm panel
ReplyDeleteSmm Panel
İş ilanları blog
İnstagram takipçi satın al
Https://www.hirdavatciburada.com/
www.beyazesyateknikservisi.com.tr
Servis
tiktok jeton hilesi
nft nasıl alınır
ReplyDeleteen son çıkan perde modelleri
minecraft premium
yurtdışı kargo
en son çıkan perde modelleri
lisans satın al
özel ambulans
uc satın al