Practicce CHARACTER FUNCTIONS

>>>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>>>