STRING FUNCTIONS
Ø Initcap
Ø Upper
Ø Lower
Ø Length
Ø Rpad
Ø Lpad
Ø Ltrim
Ø Rtrim
Ø Trim
Ø Translate
Ø Replace
Ø Soundex
Ø Concat ( ‘ || ‘ Concatenation operator)
Ø Ascii
Ø Chr
Ø Substr
Ø Instr
Ø Decode
Ø Greatest
Ø Least
Ø Coalesce
a) INITCAP
This will capitalize
the initial letter of the string.
Syntax: initcap (string)
Ex:
SQL> select
initcap('computer') from dual;
INITCAP
-----------
Computer
b) UPPER
This will convert the
string into uppercase.
Syntax: upper (string)
Ex:
SQL> select upper('computer')
from dual;
UPPER
-----------
COMPUTER
c) LOWER
This will convert the
string into lowercase.
Syntax: lower (string)
Ex:
SQL> select lower('COMPUTER') from dual;
LOWER
-----------
computer
d) LENGTH
This will give length
of the string.
Syntax: length (string)
Ex:
SQL> select length('computer')
from dual;
LENGTH
-----------
8
e) RPAD
This will allows you to
pad the right side of a column with any set of characters.
Syntax: rpad (string, length [, padding_char])
Ex:
SQL> select
rpad('computer',15,'*'), rpad('computer',15,'*#') from dual;
RPAD('COMPUTER' RPAD('COMPUTER'
---------------------- ----------------------
computer******* computer*#*#*#*
-- Default padding
character was blank space.
f) LPAD
This will allows you to
pad the left side of a column with any set of characters.
Syntax: lpad (string, length [, padding_char])
Ex:
SQL> select
lpad('computer',15,'*'), lpad('computer',15,'*#') from dual;
LPAD('COMPUTER' LPAD('COMPUTER'
--------------------- ---------------------
*******computer *#*#*#*computer
-- Default padding
character was blank space.
g) LTRIM
This will trim off
unwanted characters from the left end of string.
Syntax: ltrim (string [,unwanted_chars])
Ex:
SQL> select ltrim('computer','co'),
ltrim('computer','com') from dual;
LTRIM( LTRIM
-------- ---------
mputer puter
SQL> select
ltrim('computer','puter'), ltrim('computer','omputer') from dual;
LTRIM('C LTRIM('C
---------- ----------
computer computer
-- If you haven’t specify any unwanted
characters it will display entire string.
h) RTRIM
This will trim off
unwanted characters from the right end of string.
Syntax: rtrim (string [, unwanted_chars])
Ex:
SQL> select rtrim('computer','er'),
rtrim('computer','ter') from dual;
RTRIM( RTRIM
-------- ---------
comput compu
SQL> select
rtrim('computer','comput’), rtrim('computer','compute') from dual;
RTRIM('C RTRIM('C
---------- ----------
computer computer
-- If you haven’t
specify any unwanted characters it will display entire string.
i) TRIM
This will trim off
unwanted characters from the both sides of string.
Syntax: trim (unwanted_chars from string)
Ex:
SQL> select trim( 'i' from
'indiani') from dual;
TRIM(
-----
ndian
SQL> select trim( leading'i'
from 'indiani') from dual; -- this will
work as LTRIM
TRIM(L
------
ndiani
SQL> select trim( trailing'i'
from 'indiani') from dual; -- this will
work as RTRIM
TRIM(T
------
Indian
j) TRANSLATE
This will replace the
set of characters, character by character.
Syntax: translate (string, old_chars, new_chars)
Ex:
SQL> select translate('india','in','xy')
from dual;
TRANS
--------
xydxa
k) REPLACE
This will replace the
set of characters, string by string.
Syntax: replace (string, old_chars [, new_chars])
Ex:
SQL> select replace('india','in','xy'), replace(‘india’,’in’)
from dual;
REPLACE REPLACE
----------- -----------
Xydia dia
l) SOUNDEX
This will be used to
find words that sound like other words, exclusively used in where
clause.
Syntax: soundex (string)
Ex:
SQL> select * from emp where
soundex(ename) = soundex('SMIT');
EMPNO ENAME JOB MGR HIREDATE SAL
DEPTNO
--------
-------- ----- ----- ------------ --------- ----------
7369
SMITH CLERK 7902
17-DEC-80 500 20
m) CONCAT
This will be used to combine two strings
only.
Syntax: concat (string1, string2)
Ex:
SQL> select
concat('computer',' operator') from dual;
CONCAT('COMPUTER'
-------------------------
computer operator
If you want to combine
more than two strings you have to use concatenation
operator(||).
SQL> select 'how' || ' are' || ' you' from dual;
'HOW'||'ARE
---------------
how are you
n) ASCII
This will return the
decimal representation in the database character set of the first
character of the
string.
Syntax: ascii (string)
Ex:
SQL> select ascii('a'),
ascii('apple') from dual;
ASCII('A') ASCII('APPLE')
------------ ------------------
97 97
o) CHR
This will return the
character having the binary equivalent to the string in either the
database character set
or the national character set.
Syntax: chr (number)
Ex:
SQL> select chr(97) from dual;
CHR
-----
a
p) SUBSTR
This will be used to
extract substrings.
Syntax: substr (string, start_chr_count [, no_of_chars])
Ex:
SQL> select
substr('computer',2), substr('computer',2,5), substr('computer',3,7)
from dual;
SUBSTR( SUBST SUBSTR
---------- ------- --------
omputer omput mputer
Ø If no_of_chars parameter is negative then it will display nothing.
Ø If both parameters except string are null or zeros then it will
display nothing.
Ø If no_of_chars parameter is greater than the length of the string then
it ignores and calculates based on the orginal string length.
Ø If start_chr_count is negative then it will extract the substring from
right end.
1 2 3 4 5 6 7 8
C O M P U T E R
-8 -7 -6 -5 -4 -3 -2 -1
q) INSTR
This will allows you
for searching through a string for set of characters.
Syntax: instr (string, search_str [, start_chr_count [, occurrence]
])
Ex:
SQL> select
instr('information','o',4,1), instr('information','o',4,2) from dual;
INSTR('INFORMATION','O',4,1)
INSTR('INFORMATION','O',4,2)
------------------------------------ -------------------------------------
4 10
Ø If you are not specifying start_chr_count and occurrence then it will start
search from the beginning and finds first occurrence only.
Ø If both parameters start_chr_count and occurrence are null, it will display
nothing.
r) DECODE
Decode will act as value
by value substitution.
For every value of
field, it will checks for a match in a series of if/then tests.
Syntax: decode (value, if1, then1, if2, then2, ……. else);
Ex:
SQL> select sal,
decode(sal,500,'Low',5000,'High','Medium') from emp;
SAL
DECODE
----- ---------
500 Low
2500
Medium
2000 Medium
3500 Medium
3000 Medium
5000 High
4000 Medium
5000 High
1800 Medium
1200 Medium
2000 Medium
2700 Medium
2200 Medium
3200 Medium
SQL> select
decode(1,1,3), decode(1,2,3,4,4,6) from dual;
DECODE(1,1,3) DECODE(1,2,3,4,4,6)
-----------------
------------------------
3 6
Ø 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.
s) GREATEST
This will give the
greatest string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex:
SQL> select greatest('a', 'b',
'c'), greatest('satish','srinu','saketh') from dual;
GREAT GREAT
------- -------
c srinu
Ø If all the parameters are
nulls then it will display nothing.
Ø If any of the parameters
is null it will display nothing.
t) LEAST
This will give the least
string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex:
SQL> select least('a', 'b',
'c'), least('satish','srinu','saketh') from dual;
LEAST LEAST
------- -------
a saketh
Ø If all the parameters are
nulls then it will display nothing.
Ø If any of the parameters
is null it will display nothing.
u) COALESCE
This will gives the
first non-null string.
Syntax: coalesce (strng1, string2, string3 … stringn)
Ex:
SQL> select
coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual;
COALESCE COALESCE
----------- -----------
a a
No comments:
Post a Comment