SQL Functions

TO_DATE Function :
---------------------

The Oracle TO_DATE function converts a string to a date.

select to_date('20020315','yyyymmdd') from dual;
select to_date('07112003','mmddyyyy') from dual;


LPAD, RPAD, LTRIM, RTRIM, INSTR, SUBSTR, LOWER, UPPER, INITCAP Functions

SQL> SELECT * FROM EMP;

EMPNO ENAME                  SAL  COMM DEPTNO
----- -------------------- ----- ----- ------
 7369 SMITH                  900   100     20
 7499 ALLEN                 1600   300     30
 7521 WARD                  1250   500     30
 7566 JONES                 2975   100     20
 7654 MARTIN                2975   100     20
 7412 STONECOLD             1244   200     20
 7412 STONECOLD             1244   200     20

SQL> select substr(ename,2) from emp;

(OR)

SELECT E.*, SUBSTR(ENAME,2) SUBSTR FROM EMP E;



SUBSTR(ENAME,2)
-------------------
MITH
LLEN
ARD
ONES
ARTIN
TONECOLD
TONECOLD


SQL> select instr(ENAME,'M') from emp;

INSTR(ENAME,'M')
----------------
               2
               0
               0
               0
               1
               0
               0



SQL> SELECT UPPER(ENAME) from emp;

UPPER(ENAME)
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
STONECOLD
STONECOLD


SQL> SELECT LOWER(ENAME) FROM EMP;

LOWER(ENAME)
--------------------
smith
allen
ward
jones
martin
stonecold
stonecold


SQL> SELECT INITCAP(ENAME) FROM EMP3;

INITCAP(ENAME)
--------------------
Balu



SQL> SELECT LTRIM(ENAME,'S') FROM EMP; OR SUBSTR(Field1, INSTR(Field1,':')+1)

LTRIM(ENAME,'S')
--------------------
MITH
ALLEN
WARD
JONES
MARTIN
TONECOLD
TONECOLD



SQL> SELECT LTRIM(ENAME,'CSS-, *^') LTRIM FROM EMP1;

LTRIM
-------
KING
BLAKE
LARK
JONES
OTT
FORD
MITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
KING%$

KING-AXYZ


SQL> SELECT RTRIM(ENAME,'D') FROM EMP;

RTRIM(ENAME,'D')
--------------------
SMITH
ALLEN
WAR
JONES
MARTIN
STONECOL
STONECOL


SQL> SELECT LTRIM(ENAME,'S,M'), RTRIM(ENAME,'H,N') FROM EMP;

LTRIM(ENAME,'S,M')   RTRIM(ENAME,'H,N')
-------------------- --------------------
ITH                  SMIT
ALLEN                ALLE
WARD                 WARD
JONES                JONES
ARTIN                MARTI
TONECOLD             STONECOLD
TONECOLD             STONECOLD


SQL> SELECT LPAD(ENAME,10,'0') FROM EMP;

LPAD(ENAME
----------
00000SMITH
00000ALLEN
000000WARD
00000JONES
0000MARTIN
0STONECOLD
0STONECOLD


SQL> SELECT RPAD(ENAME,10,'0') FROM EMP;

RPAD(ENAME
----------
SMITH00000
ALLEN00000
WARD000000
JONES00000
MARTIN0000
STONECOLD0
STONECOLD0