How to get file extension of a file as a result of SQL query?

Question

I have a table called datas and I execute a query like this:

 SELECT linkurl AS DOWNLOADURL, lastrevlevel AS VERSION, code AS DESCRIPTION, created AS RELEASEDATE, name AS TYPE FROM datas WHERE id IN (SELECT child_id FROM datas _datas WHERE parent_id = (SELECT Max(id) FROM datas WHERE code = 'AN4307SW')) 

It returns the results as follows:

 DOWNLOADURL VERSION DESCRIPTION RELEASEDATE TYPE /artifacts/download.txt 2.0 images 25/6/12 download.txt 

In the Type column, I get the file name. I need to get the file name file extension in the Type column. How can I achieve this?

Examples:

 TYPE .txt .pdf .xls 

Answer 1

You can use SUBSTRING_INDEX . Like this:

 select linkurl as DOWNLOADURL,lastrevlevel as VERSION, code as DESCRIPTION,created as RELEASEDATE, SUBSTRING_INDEX(name,'.',-1) as TYPE from datas where id in (select child_id from datas _datas where parent_id=( select max(id) from datas where code = 'AN4307SW')) 

EDIT

If you see documents on this feature, I think it will meet your requirements.

Returns the substring from the string str before counting the occurrences of the delimiter. If the number is positive, everything is returned to the left of the trailing separator (counting to the left). If the counter is negative, everything is returned to the right of the ending delimiter (counting to the right). SUBSTRING_INDEX () performs case-sensitive searches when searching for a delimiter.

This will also handle a case like this:

 select SUBSTRING_INDEX('Test.Document.doc','.',-1); 

EDIT2

If you are using an oracle. Please mark the question in the correct question next time. There SUBSTRING_INDEX no SUBSTRING_INDEX . But what I see, you can do it quite easily:

 SELECT SUBSTR('Test.Document.doc', INSTR('Test.Document.doc', '.',-1)) FROM dual; 

Full query like this:

 select linkurl as DOWNLOADURL,lastrevlevel as VERSION, code as DESCRIPTION,created as RELEASEDATE, SUBSTR(name, INSTR(name, '.',-1)) as TYPE from datas where id in (select child_id from datas _datas where parent_id=( select max(id) from datas where code = 'AN4307SW')) 

Link here

Answer 2

 select linkurl as DOWNLOADURL,lastrevlevel as VERSION, code as DESCRIPTION,created as RELEASEDATE,reverse(substring(reverse(name), 1,charindex('.', reverse(name))-1)) as TYPE from datas where id in (select child_id from datas _datas where parent_id=( select max(id) from datas where code = 'AN4307SW')) 

Answer 3

I think you will need something like this

 SELECT REPLACE(name,SUBSTRING(name ,0, CHARINDEX('.', name )),'') 

Answer 4

 SELECT SUBSTRING(file_name,(LENGTH(file_name)-LOCATE('.',REVERSE(file_name)))+2) FROM <table name> WHERE file_id=<file_id>; 

Answer 5

 SELECT REVERSE(SUBSTRING(REVERSE(name),1,LOCATE('.',REVERSE(name),1‌​))); 

Answer 6

It can be done like this

 SELECT SUBSTRING_INDEX(FILE_NAME,"." ,-1) from TABLE_NAME