>>>Previous>>>
CHARACTER FUNCTIONS: These functions operate on character data.
UPPER( str) : It converts all letters in the given string “str” into Upper case.
SQL> SELECT UPPER('abcDEfg') FROM DUAL;
UPPER('
-------
ABCDEFG
LOWER(str): It converts all the letters in the given string “str” into Lower Case.
SQL> SELECT LOWER('ABCDEfg') FROM DUAL;
LOWER('
-------
abcdefg
INITCAP(str): It converts first letter of every word in the given string “str” into Upper Case and remaining letters into lower case. It is like proper function in FoxPro.
SQL> SELECT INITCAP('ABCDEF') FROM DUAL;
INITCA
------
Abcdef
LENGTH(str) : This function returns the number of characters in the given string (including spaces)
SQL> SELECT LENGTH('ABCD') FROM DUAL;
LENGTH('ABCD')
--------------
4
SQL> SELECT LENGTH('AB CD') FROM DUAL;
LENGTH('ABCD')
--------------
5
SUBSTR(str, m, n) :
Will extract “n” characters from the given string starting from
“m th” position.
SQL> SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL
SUB
---
BCD
SQL> SELECT SUBSTR('ABCDEF',1,3) FROM DUAL
SUB
---
ABC
INSTR(string, str): It displays the location of “str” in the given string “string” .
SQL> SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS') FROM DUAL;
INSTR('
-----------------------------------------------------
27
INSTR(string, str, m, n):
It displays nth occurrence of “str” in the string “string” starting from “m”.
SQL> SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS',1,2) FROM DUAL
INSTR(
------------------------
37
Note : DUAL IS A TABLE WITH 1 COLUMN AND 1 ROW OF DATA IN IT.
SQL > DESC DUAL
Name Null? Type
----------------------------------------- -------- -
DUMMY VARCHAR2(1)
SQL > SELECT * FROM DUAL;
DUMMY
-
X
LPAD() :
This function is used to left pad the the given string with specified character or string.
SQL > SELECT LPAD('BCD',4,'A') FROM DUAL;
LPAD
----
ABCD
Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 4 characters.
SQL > SELECT LPAD('BCD',5,'A') FROM DUAL;
LPAD(
-----
AABCD
Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 5 characters.
SQL > SELECT LPAD('BCD',3,'A') FROM DUAL;
LPA
---
BCD
Explanation: To the given string "BCD" add "A" to the left necessary number of times to make it a string of 3 characters.
SQL > SELECT LPAD(' ',ROWNUM,'*') FROM EMP;
LPAD('',ROWNUM,'*')
-----------------------------------------------------------------
*
**
***
****
*****
******
*******
********
*********
**********
***********
************
*************
RPAD(): This function is used to left pad the the given string with specified character or string.
SQL > SELECT RPAD('BILL ' , 12 , 'CLINTON') FROM DUAL;
RPAD('BILL',
------------
BILL CLINTON
LTRIM(): This function removes specified string from the given string if it is there to the left of given string.
SQL > SELECT LTRIM('GEORGE BUSH', 'GEORGE') FROM DUAL;
LTRIM
-----
BUSH
RTRIM(): This function removes specified string from the given string if it is there to the right of given string.
SQL > SELECT RTRIM('TONY BLAIR', 'AIR') FROM DUAL;
RTRIM('
-------
TONY BL
ASCII(): Displays equivalent ASCII value of a character.
SQL > SELECT ASCII('A') FROM DUAL;
ASCII('A')
----------
65
SQL >SELECT TRANSLATE('JOHN','H','N') FROM DUAL;
TRAN
----
JONN
>>>Next>>>