CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));

INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');

INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');

INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');

INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');

INSERT INTO LEAVE_DYS VALUES ('R0313','LEAVE SALARY FOR SEPT. 9, 2010 - SEPT. 8, 2012 (35 ABSENTS)');

COMMIT;



SELECT * FROM LEAVE_DYS;



LV_EMP_CODE	LV_DESC
R0754	LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)
R0667	LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)
R0841	LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)
R0854	LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)
R0313	LEAVE SALARY FOR SEPT. 9, 2010 - SEPT. 8, 2012 (35 ABSENTS)

--Required output is

LV_EMP_CODE  LV_DESC
R0754        16/04/2012
R0667        14/07/2012
R0841        29/05/2012
R0854        29/05/2012
R0313        09/09/2012

SELECT ltrim(rtrim(replace(TO_CHAR((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))) A 
  FROM leave_dys  


A
APRIL 16, 2010
JULY 14, 2009
MAY 29, 2010
MAY 29, 2010
SEPT 9, 2010

--when i am converting it to date by using to_date its giving an error , ORA-01858 a non numeric character was found when numeric was expected.

SELECT to_date(ltrim(rtrim(replace(TO_CHAR((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))) ) A 
  FROM leave_dys  

SELECT TO_DATE(ltrim(rtrim(replace(((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))),'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')  A 
  FROM leave_dys

A
04/16/2010 00:00:00
07/14/2009 00:00:00
05/29/2010 00:00:00
05/29/2010 00:00:00
09/09/2010 00:00:00

SELECT TO_DATE(
               REGEXP_REPLACE(
                              lv_desc,
                              '^LEAVE SALARY FOR (...)[^ ]* (\d\d?, \d{4}).*$',
                              '\1 \2'
                             ),
               'MON DD, YYYY',
               'NLS_DATE_LANGUAGE = American'
              ) A 
  FROM  leave_dys
/

A
---------
16-APR-10
14-JUL-09
29-MAY-10
29-MAY-10
09-SEP-10

SQL>