Character Functions In Oracle with Examples

Character functions accept character input and return either character or number.Below are few of the examples

• INITCAP

Select  initcap('hello') from dual;

INITC

-----

Hello

• UPPER & LOWER

SQL> select upper('hello') from dual;

UPPER

-----

HELLO

SQL> select lower('HEL') from dual;

LOW

---

Hel

• LTRIM

SQL> select ltrim('hhhhhHEL','hhh') from dual;

LTR

---

HEL

• REPLACE

SQL> select replace('Jack and Jue','J','bl') from dual;

REPLACE('JACKA

--------------

black and blue

• LPAD

SQL> select lpad('function',12,'=') from dual;

LPAD('FUNCTI

------------

====function

• RPAD

SQL> select rpad('function',12,'=') from dual;

RPAD('FUNCTI

------------

function====

• TRIM

SQL> select trim(leading 9 from 9998999999999999999) from dual;

TRIM(LEADING9FRO

----------------

8999999999999999

SQL> select trim(trailing 9 from 9998999999999999999) from dual;

TRIM

----

9998

SQL> select trim(9 from 9998999999999999999) from dual;

T

-

8

• LENGTH

SQL> select length('99999999999888888888877777777') from dual;

LENGTH('99999999999888888888877777777')

---------------------------------------

                                     29

SQL> select length('HELLO') from dual;

LENGTH('HELLO')

--------------------------

                        5

• INSTR

SQL> SELECT INSTR('CORPORATE FLOOR','O',1,2) FROM DUAL;

INSTR('CORPORATEFLOOR','O',1,2)

-------------------------------

                              5