In Our Previous post,we learnt about Numeric Functions and now lets learn about string functions.
STRING FUNCTIONS
We have following types of string functions in SQL.
a) Initcap
b) Upper
c) Lower
d) Length
e) Rpad
f) Lpad
g) Ltrim
h) Rtrim
i) Trim
j) Translate
k) Replace
l) Concat ( ‘ || ‘ Concatenation operator)
m) Ascii
n) Chr
o) Substr
p) Instr
q) Decode
r) Greatest
s) Least
t) 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) 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
m) 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
n) 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
o) 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
1)If no_of_chars parameter is negative then it will display nothing.
2)If both parameters except string are null or zeros then it will display nothing.
3)If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the original string length.
4)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
p) 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
1) If you are not specifying start_chr_count and occurrence then it will start search from the beginning and finds first occurrence only.
2) If both parameters start_chr_count and occurrence are null, it will display nothing.
q) 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
1) If the number of parameters are odd and different then decode will display nothing.
2) If the number of parameters are even and different then decode will display last value.
3) If all the parameters are null then decode will display nothing.
4) If all the parameters are zeros then decode will display zero.
r) GREATEST
This will give the greatest string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex: SQL> select greatest('a', 'b', 'c'), greatest('satish','srinu','Ram') from dual;
GREAT GREAT
------- -------
c srinu
1) If all the parameters are nulls then it will display nothing.
2) If any of the parameters is null it will display nothing.
s) LEAST
This will give the least string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex: SQL> select least('a', 'b', 'c'), least('satish','srinu','Ram') from dual;
LEAST LEAST
------- -------
a Ram
1)If all the parameters are nulls then it will display nothing.
2) If any of the parameters is null it will display nothing.
t) COALESCE
This will gives the first not-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
STRING FUNCTIONS
We have following types of string functions in SQL.
a) Initcap
b) Upper
c) Lower
d) Length
e) Rpad
f) Lpad
g) Ltrim
h) Rtrim
i) Trim
j) Translate
k) Replace
l) Concat ( ‘ || ‘ Concatenation operator)
m) Ascii
n) Chr
o) Substr
p) Instr
q) Decode
r) Greatest
s) Least
t) 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) 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
m) 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
n) 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
o) 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
1)If no_of_chars parameter is negative then it will display nothing.
2)If both parameters except string are null or zeros then it will display nothing.
3)If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the original string length.
4)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
p) 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
1) If you are not specifying start_chr_count and occurrence then it will start search from the beginning and finds first occurrence only.
2) If both parameters start_chr_count and occurrence are null, it will display nothing.
q) 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
1) If the number of parameters are odd and different then decode will display nothing.
2) If the number of parameters are even and different then decode will display last value.
3) If all the parameters are null then decode will display nothing.
4) If all the parameters are zeros then decode will display zero.
r) GREATEST
This will give the greatest string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex: SQL> select greatest('a', 'b', 'c'), greatest('satish','srinu','Ram') from dual;
GREAT GREAT
------- -------
c srinu
1) If all the parameters are nulls then it will display nothing.
2) If any of the parameters is null it will display nothing.
s) LEAST
This will give the least string.
Syntax: greatest (strng1, string2, string3 … stringn)
Ex: SQL> select least('a', 'b', 'c'), least('satish','srinu','Ram') from dual;
LEAST LEAST
------- -------
a Ram
1)If all the parameters are nulls then it will display nothing.
2) If any of the parameters is null it will display nothing.
t) COALESCE
This will gives the first not-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