STRING FUCNCTIONS:
-------------------------------
1.LOWER:this
function will convert into given
function in to lower case
========
SELECT
LOWER(ENAME) FROM EMP;
2.UPPER:this
function will convert into given function in to upper case
========
SELECT
UPPER('mahamad') FROM DUAL;
3.INITCAP:this
function capitalize the initial letter of the string.
==========
SELECT
INITCAP(ENAME) FROM EMP;
4.LENGTH:this
function will give the length of the string.
=========
SELECT
LENGTH('MAHAMAD') FROM DUAL;
5.RPAD:this
function will allows us to pad the right side of a column with any set of
characters.
=======
SELECT
'MAHAMAD',RPAD('MAHAMAD','10','*') FROM DUAL;
6.LPAD:this
function will allows us to pad the left side of a column with any set of
characters.
=======
SELECT
'MAHAMAD',LPAD('MAHAMAD','10','*') FROM DUAL;
7.LTRIM:this
function will trim off unwanted characters from left end of string.
========
if you haven't specified any unwanted
characters it will display entire string.
SELECT
LTRIM('computer','co'),LTRIM('computer','CO'),LTRIM('computer','puter') from
dual;
8.RTRIM:this
function will trim off unwanted characters from right end of string.
========
if you haven't specified any unwanted
characters it will display entire string.
SELECT
RTRIM('computer','puter'),RTRIM('computer','PUTER'),RTRIM('computer','com')
FROM DUAL;
9.TRIM:this
function will trimm off unwanted characters from the both side of the string.
=======
trim
set should have only one character.
SELECT
TRIM('i' FROM 'indiani') FROM DUAL;
SELECT
TRIM(leading 'i' FROM 'indiani') FROM DUAL;--it will work as a LTRIM.
SELECT
TRIM(trailing 'i' FROM 'indiani') FROM DUAL;--it will work as a RTRIM.
10.TRANSLATE:this
function will replace the set of characters, character by character.
=============
TRANSLATE(string,old
char,new chars);
SELECT
TRANSLATE('india','in','xy') FROM DUAL;
o/p:xydxa
11.REPLACE:this
function will replace the set of characters, string by string.
===========
REPLACE(string,old
char,[new char])
SELECT
REPLACE('india','in','xy') FROM DUAL;
o/p:xydia
Examples:
select replace(translate('CDSE567ABCGD123ABCDG45ABC098GFDTABC','ABCDEFGHIJKLMNOPQRSTUVWXYZ','
'),'','') FROM DUAL;
select
translate('CDSE567ABCGD123ABCDG45ABC098GFDTABC','ABCDEFGHIJKLMNOPQRSTUVWXYZ','
') from dual;
select
replace(translate('adg543','543',' '),' ','') from dual;
12.SOUNDEX:this
function returns phonetic representation.
===========
13.ASCII:
This function will return the decimal representation in database character set
of first character
========
of the string.
SELECT
ASCII(A) FROM DUAL;
SELECT
ASCII('ALI') FROM DUAL;
14.CHR:This
function will return the character having the binary equalant of the input in
either the
======
database character set or national character set.
SELECT
CHAR(97) FROM DUAL;
Example:
SELECT
'first_name='||SUBSTR('anka babu',1,INSTR ('anka babu','
',1))||chr(10)||'last_name='||SUBSTR('anka babu',INSTR ('anka babu','
',1)+1,LENGTH('anka babu'))LASTNAME FROM DUAL;
15.SUBSTR:This
function is used to extract the particular portion of the string.
==========
SUBSTR('string','<string
position'>,<'no.of characters to be extract'>) FROM DUAL;
SELECT
SUBSTR('mahamad123','1','7') FROM DUAL;
16.INSTR:this
function is used to extract the position of the character in given string.
========
INSTR('string',<'character
to search>,<starting position>,<occurrence number>);
SELECT
INSTR('mahamad123','m',1,2) FROM DUAL;
SELECT
SUBSTR('&&ANKABABU',1,INSTR ('&ANKABABU',' ',1)) FROM DUAL;
17.DECODE:
==========
• Decode is used to check for multiple
conditions while manipulating or retrieving the data.
• It implements “IF” construct logic
• If the number of parameters are odd
and different then decode will display nothing.
• If the number of parameters are even
and different then decode will display last value.
• If all the parameters are null then
decode will display nothing.
• If all the parameters are zeros then
decode will display zero
syntax:
DECODE(value,if1,then1,if2,then2...else)
SELECT
SAL DECODE(SAL,500,'LOW',5000,'HIGH','MEDIUM') FROM EMP ;
SELECT
ENAME,SAL,DEPTNO,DECODE(DEPTNO,10,SAL*0.15,20,SAL*0.25,30,SAL*0.35,SAL*0.45)BONUS
FROM EMP;
18.GREATEST:this
will give the greatest string.
============
SELECT
GREATEST('a','b',c') from dual;
SELECT
GREATEST('ali','chitu','venky') from dual;
*if
all parameters null it will display nothing
*if
any parameter is null it will display nothing.
SELECT
GREATEST('A','B',' ') FROM DUAL;
SELECT
GREATEST(' ') FROM DUAL;
19.LEAST:this
will give the least string.
=========
SELECT
LEAST('A','B','C') FROM DUAL;
SELECT
LEAST('ali','chitu','venky')from dual;
*if
all parameters null it will display nothing
*if
any parameter is null it will display nothing.
SELECT
GREATEST('A','B',' ') FROM DUAL;
SELECT
GREATEST(' ') FROM DUAL;
20.COALESCE:this
function will give the first not null.
============
SELECT
COALESCE('a,'b','c') from dual;
SELECCT
COALESCE(null,'a',null,'b') from dual;
NUMBER FUNCTIONS:
---------------------------------
1.ABS:
it is always positive number
2.SIGN:
SIGN function returns a value indicating the sign of a number.
If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.
3.SQRT:this
will give the square root of the given value. Input value must be positive.
SQRT(value)
SELECT SQRT(2),SQRT(4),SQRT(9) FROM DUAL;
4.MOD:this
will give the reminder.
MOD(value,divisor);
MOD(4,2);
2)4(2--divisor
4
---
0 --Reminder
---
ex: MOD(NULL,NULL),MOD(0,0),MOD(1,4)
5.NVL:NVL
replace the null value with the specified value. NVL function takes two input
parameters.
if first parameter is null then it will
return the second parameter other wise it will return the first parameter
SELECT
NVL(NULL,500),NVL(NULL,NULL),NVL(100,10);
SELECT
ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP;
SELECT VENDOR_SITE_ID,VENDOR_ID,VENDOR_SITE_CODE,ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,CITY,STATE FROM AP_SUPPLIER_SITES_ALL WHERE ROWNUM<20
SELECT VENDOR_SITE_ID,VENDOR_ID,
'VENDORSITECODE :' ||VENDOR_SITE_CODE ||CHR(13) ||
NVL2(ADDRESS_LINE1,
'ADDRESS1 :' ||ADDRESS_LINE1 ||CHR(13),NULL) ||
NVL2(ADDRESS_LINE2,
'ADDRESS2 :' ||ADDRESS_LINE2 ||CHR(13),NULL) ||
NVL2(ADDRESS_LINE3,
'ADDRESS3 :' ||ADDRESS_LINE3 ||CHR(13),NULL) ||
NVL2(CITY, 'CITY :' ||CITY ||CHR(13),NULL) ||
NVL2(STATE, 'STATE :' ||STATE ||CHR(13),NULL)
AS SUB_ADDRESS
FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID=4403;
6.POWER:power
function is used to raise a value to a given exponent.
POWER(value,exponent);
POWER(2,3),POWER(1,1),POWER(-2,-3),POWER(null,null);
7.EXP:
The SQL EXP() returns e raised to
the n-th power(n is the numeric expression),
where e is the base of natural
algorithm and the value of e is approximately 2.71828183.
HERE EXP(0) means e0=1
EXP(1) means e*1= 2.71828183.
EXP(2) means
e*e= ( 2.71828183)*(2.71828183)=7.3890561
EXP(-2) means 1/(e*e)=0.135335283236613
8.LN():
The SQL LN() function returns the natural
logarithm of n,
where n is greater than 0 and its base is a number equal to
approximately 2.71828183.
LN
and EXP are reciprocal to each other.
EXP
(3) = 20.0855369
LN
(20.0855369) = 3
9.LOG:
this function based on 10 based logarithms. Input value must be greater than
zero which is positive only.
LOG(10,value);
LOG(10,10),LOG(10,1),LOG(10,null);
10.CIEL:this
function will produce whole number that is greater than or equal to the
specified value.
select
CIEL(5.1),CIEL(5),CIEL(-5),CIEL(0),CIEL(NULL) FROM DUAL;
11.FLOOR:this
function will produce whole number that is less than or equal to the specified
value.
select
FLOOR(5.1),FLOOR(5),FLOOR(-5),FLOOR(0),FLOOR(NULL) FROM DUAL;
12.ROUND:
this function will round number to given number of digits of precision.
ROUND(value, precision);
SELECT
ROUND(123.2345),ROUND(123.2345,2),ROUND(123.2354) FROM DUAL;
No comments:
Post a Comment