MySQL 作为sql查询的结果，如何获取文件的文件扩展名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10945568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get file extension of file as a result of sql query?
提问by anto
I have a table named datasand 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 Typecolumn I am geting name of the file. I need to get the file extension of the file name in the Typecolumn. How can I achieve this?
在Type列中，我正在获取文件的名称。我需要获取列中文件名的文件扩展名Type。我怎样才能做到这一点？
Examples:
例子：
TYPE
.txt
.pdf
.xls
回答by Arion
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 为正数，则返回最终分隔符左侧的所有内容（从左侧开始计数）。如果计数为负，则返回最终分隔符右侧的所有内容（从右侧开始计数）。SUBSTRING_INDEX() 在搜索 delim 时执行区分大小写的匹配。
This will also handle a case like this:
这也将处理这样的情况：
select SUBSTRING_INDEX('Test.Document.doc','.',-1);
EDIT2
编辑2
If you are using oracle. Please tag the question in the correct matter next time. There is no SUBSTRING_INDEXin oracle. But what I can see you can do this quite easy:
如果您使用的是oracle。下次请在正确的问题中标记问题。SUBSTRING_INDEXoracle中没有。但是我可以看到你可以很容易地做到这一点：
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
参考这里
回答by Alpesh Prajapati
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'))
回答by Rohan Büchner
think you'll need something like this
认为你需要这样的东西
SELECT REPLACE(name,SUBSTRING(name ,0, CHARINDEX('.', name )),'')
回答by Muhammed Rafi .K.A
It can be done like this
可以这样做
SELECT SUBSTRING_INDEX(FILE_NAME,"." ,-1) from TABLE_NAME
回答by Sebin
SELECT
SUBSTRING(file_name,(LENGTH(file_name)-LOCATE('.',REVERSE(file_name)))+2)
FROM <table name> WHERE file_id=<file_id>;
回答by mansi
SELECT REVERSE(SUBSTRING(REVERSE(name),1,LOCATE('.',REVERSE(name),1??)));
