Friday, October 28, 2011
ORACLE SQL TUTORIAL: STRING FUNCTIONS
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: STRING FUNCTIONS”
Post a Comment