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

Question

I have a table named datas and I'm executing 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 results like this:

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

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

Examples:

TYPE
.txt
.pdf
.xls

What would you expect to happen if I had a file named `Test.Document.doc` ? What about if no extension was specified? — Bridge, Jun 08 '12 at 08:44
@anto So the substring after the last dot - in my example `doc`. Is it possible that there could be a file without an extension (not containing a dot at all?) — Bridge, Jun 08 '12 at 09:18
@bridge: yes there is a chance of not getting dot, then how should i proceed — anto, Jun 08 '12 at 09:23
@anto In that case, the accepted answer which uses `SUBSTRING_INDEX` will return the whole name of the file if there is no dot. Is that acceptable? — Bridge, Jun 08 '12 at 09:41
@Bridge: but i am having certain other fields which would definitely have extension at last. — anto, Jun 08 '12 at 09:59

score 34 · Accepted Answer · edited May 23 '17 at 11:54

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 the docs on this function I think this will apply well to you requirements.

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

This will also handle a case like this:

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

EDIT2

If you are using oracle. Please tag the question in the correct matter next time. There is no SUBSTRING_INDEX in oracle. But what I can see you can do this quite easy:

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'))

Reference here

thnks but when i execute this query its showing invalid identifier SUBSTRING_INDEX .. i am using oracle — anto, Jun 08 '12 at 09:07
@arion: thanks it works well,if i need .txt,.pdf,.xls what should i do? — anto, Jun 08 '12 at 09:25
@anto : I update the answer for that. You need to remove the +1. Have a look — Arion, Jun 08 '12 at 09:26
this is super helpful, thanks from 10 years in the future! just want to point out that SUBSTRING_INDEX(name,'.',-1) yields the file extension _without_ a dot, whereas OP wanted it _with_ a dot. — Zev Averbach, Jun 18 '22 at 22:27

score 2 · Answer 2 · answered Jun 08 '12 at 08:48

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'))

answered Jun 08 '12 at 08:48

Alpesh Prajapati

1,593
2
18
38

...just tried this solution works well, also covers scenarios where a file name might look like this 'some.thing.txt' – Rohan Büchner Jun 08 '12 at 09:08

score 1 · Answer 3 · answered Jun 08 '12 at 08:57

1

think you'll need something like this

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

answered Jun 08 '12 at 08:57

Rohan Büchner

5,333
4
62
106

select REVERSE(SUBSTRING(REVERSE(name),1,LOCATE('.',REVERSE(name),1))) – mansi Oct 18 '17 at 12:01
I want to post it as another ans but its get posted as comment here wrongly. but how do i delete this comment ? there is no option to delete – mansi Oct 23 '17 at 10:08

score 1 · Answer 4 · edited Sep 08 '15 at 15:08

1

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

edited Sep 08 '15 at 15:08

Sjon

4,989
6
28
46

answered Sep 08 '15 at 14:23

Sebin

11
1

score 1 · Answer 5 · edited Oct 18 '17 at 18:38

1

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

edited Oct 18 '17 at 18:38

Rohan Büchner

5,333
4
62
106

answered Oct 18 '17 at 12:01

mansi

837
5
12

Muhammed Rafi .K.A · Answer 6 · 2019-06-30T05:08:17.057

1

It can be done like this

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

edited Jun 30 '19 at 05:08

answered Jun 30 '19 at 04:59

Muhammed Rafi .K.A

29
2

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

6 Answers6