Regarding sql instr function

Oracle 10g is the db. The below query fails when extracting the date.

SELECT  TO_CHAR ( TO_DATE ( SUBSTR (file_name , INSTR (file_name , '_', -1, 2)+ 2, 8), 'YYYYMMDD'), '开发者_JS百科DD-MM-YYYY')  from dual;

I noticed we receive the below two file name of different naming formats.

660.ASSD.0063.20100923.0409.PTH2.IAC1.gcr_H201009231416151671_bbor.ddr 660.ASSD.M2K_20110309121547_489.ddr

For one file the above query works . The other file 660.ASSD.M2K_20110309121547_489.ddr it extracts "01103091" and does a to_date fails. How can i modify this query so it works for both the file formats.

Use REGEXP_SUBSTR

select TO_CHAR(TO_DATE(SUBSTR(REGEXP_SUBSTR('660.ASSD.0063.20100923.0409.PTH2.IAC1.gcr_H201009231416151671_bbor.ddr', '\d{14,17}'), 0, 8), 'yyyymmdd'), 'dd-mm-yyyy')
  from dual;
select TO_CHAR(TO_DATE(SUBSTR(REGEXP_SUBSTR('660.ASSD.M2K_20110309121547_489.ddr', '\d{14,17}'), 0, 8), 'yyyymmdd'), 'dd-mm-yyyy')
  from dual;

You can also use REGEXP_REPLACE to strip-out letters from the file name.

SELECT  TO_CHAR ( TO_DATE ( SUBSTR (regexp_replace(file_name, '[A-Z][a-z]', '')
                           , INSTR (regexp_replace(file_name, '[A-Z][a-z]', '') , '_', -1, 2)+ 1, 8), 'YYYYMMDD'), 'DD-MM-YYYY')
  FROM dual;