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,'*')
-----------------------------------------------------------------

*
**
***
****
*****
******
*******
********
*********
**********
***********
************
*************

Next Page: Tutorial 4: SQL functions - Page 3