Get document type from field with full name

Question

There is a Documents table with a DOC_FNAME field in which the full names of documents with their type are stored.

For example:

 +---------------+ |abrahadabra.pdf| |---------------| |some.txt | |---------------| |Doc.docx | +---------------+

You need a query to get the document type so that it is:

 +------+ |.pdf | |------| |.txt | |------| |.docx | +------+

I try this:

 SELECT ( SELECT (substr( d.DOC_FNAME, length(d.DOC_FNAME) - instr('.', d.DOC_FNAME)+1)) FROM dual) AS doctype FROM Documents d GROUP BY d.DOC_FNAME;

but I do not get the desired result. Please help.

try instead of ( SELECT (substr( d.DOC_FNAME, length(d.DOC_FNAME) - instr('.', d.DOC_FNAME)+1)) FROM dual) just the condition substr(d.DOC_FNAME, instr('.', d.DOC_FNAME))

Accepted Answer · 2016-09-28T08:03:04

 select substr(DOC_FNAME,-instr(reverse(DOC_FNAME),'.')) from table

Pay attention to the reverse function, it expands the line backwards to get the position of the last point, because there can be several points in modern filenames. The substr function is able to cut off from the end of the line; for this, a negative position is used.

Option 2, using regular expressions (for Oracle 10g +):

 select regexp_substr(DOC_FNAME,'\.[^.]*$') from table

Your option returns returns the full file name, does not truncate to type.

Get document type from field with full name

1 answer 1

More articles: