如何通过sql查询获取文件的文件扩展名？

I have a table named datas and I'm executing a query like this:

我有一个名为datas的表，我正在执行这样的查询：

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?

在“类型”列中，我正在确定文件的名称。我需要在Type列中获取文件名的文件扩展名。我怎样才能做到这一点？

Examples:

例子：

TYPE
.txt
.pdf
.xls

5 个解决方案

#1

You can use SUBSTRING_INDEX. Like this:

您可以使用SUBSTRING_INDEX。喜欢这个：

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.

在分隔符delim的计数出现之前，从字符串str返回子字符串。如果count为正数，则返回最终分隔符左侧的所有内容（从左侧开始计算）。如果count为负数，则返回最终分隔符右侧的所有内容（从右侧开始计算）。搜索delim时，SUBSTRING_INDEX（）执行区分大小写的匹配。

This will also handle a case like this:

这也将处理这样的情况：

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

EDIT2

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:

如果您正在使用oracle。请在下次正确的问题上标记问题。 oracle中没有SUBSTRING_INDEX。但我能看到你可以很容易地做到这一点：

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

参考这里

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

#3

think you'll need something like this

认为你需要这样的东西

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

#4

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

#5

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

#1