How to get specific value from specific position by substr-instr

Question

I have some filename like 'abc-type-bank-20200112-1578796204118.csv' i want to get the value 'bank' from above which is 3rd value if I cut the string by '-'.
If my filename is like abc-type-XYZ-20200112-1578796204118.csv, I should get 'XYZ' from 3rd position.

I was trying to get by below code, but it giving wrong value.

select substr(v_FILE_NAME,1,instr(v_FILE_NAME, '-') + 3) as name from (
select 'abc-type-bank-20200112-1578796204118.csv' as v_FILE_NAME from dual);

Answer 1

To use SUBSTR and INSTR to extract your desired substring you'd do something like

WITH cteData AS (SELECT 'abc-type-bank-20200112-1578796204118.csv' AS FILENAME FROM DUAL),
     ctePos  AS (SELECT FILENAME,
                        INSTR(FILENAME, '-', 1, 2) AS DASH_2_POS,
                        INSTR(FILENAME, '-', 1, 3) AS DASH_3_POS
                   FROM cteData)
SELECT SUBSTR(FILENAME, DASH_2_POS + 1, DASH_3_POS - DASH_2_POS - 1) AS SUBSTR_3
  FROM ctePos

The above will extract the substring 'bank' from the sample data.

db<>fiddle here