Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 763
anto Avatar asked Jun 08 '12 08:06

anto


People also ask

What is the file extension of a database file?

What Is a DB File? The . DB file extension is often used by a program to indicate that the file is storing information in some kind of structured database format.

What file extension does SQL use?

SQL Server supports two types of format files: XML formats and non-XML format files. Both non-XML format files and XML format files contain descriptions of every field in a data file, and XML format files also contain descriptions of the corresponding table columns.

How do I remove filename extension in SQL Server?

From w3schools.com/sql/func_sqlserver_charindex.asp: "The position where the search will start (if you do not want to start at the beginning of string). The first position in string is 1." If you have Strings inside your column that have more than 999 chars you have to modify this parameter.


2 Answers

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

like image 135
Arion Avatar answered Sep 24 '22 20:09

Arion


SELECT REVERSE(SUBSTRING(REVERSE(name),1,LOCATE('.',REVERSE(name),1‌​)));
like image 23
mansi Avatar answered Sep 22 '22 20:09

mansi