counteragent0090 counteragent0090 - 1 month ago 9
SQL Question

Need to extract a string from a filename

I have a column in a staging table where i am inserting the filenames. The format for the filename is as follows:

T225674_<suppliercode><YYYYMMDD>-<HHMM>_**<TIMBE NUMBER>**.in


Example:

T225674_789020161010-0900_12345.in


(I need to extract 12345 from the above string, the string can be anything and of whatever length)

I need to extract
<TIMBE NUMBER>
from the above filename, could you please let us know how to do it in Oracle SQL.

Thanks

Answer

I'm sure there's a regexp that does this but that's not my strongest point. However, a combination of instr and substr will get you there:

substr(string, instr(string, '_', -1) + 1, instr(string, '.') - instr(string, '_', -1) - 1))
Comments