Monday 17 September 2012

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

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis