SQLite:如何选择字符串的一部分? [英] SQLite: How to select part of string?

问题描述

有一个包含文件名的表列:image1.jpg，image12.png，script.php，.htaccess，...

There is table column containing file names: image1.jpg, image12.png, script.php, .htaccess,...

我只需要选择文件扩展名.我更喜欢这样:

I need to select the file extentions only. I would prefer to do that way:

SELECT DISTINCT SUBSTR(column,INSTR('.',column)+1) FROM table

但是我的SQLite版本不支持INSTR.

but INSTR isn't supported in my version of SQLite.

是否可以不使用INSTR函数来实现它?

Is there way to realize it without using INSTR function?

推荐答案

下面是查询(经过测试和验证) 仅用于选择文件扩展名.您的文件名可以包含任意数量的.字符-仍然可以使用

below is the query (Tested and verified) for selecting the file extentions only. Your filename can contain any number of . charenters - still it will work

选择不同的replace(column_name，rtrim(column_name， 从table_name替换(column_name，'.'，''))，'');

select distinct replace(column_name, rtrim(column_name, replace(column_name, '.', '' ) ), '') from table_name;

column_name是您拥有文件名的列的名称(文件名可以具有多个.的

column_name is the name of column where you have the file names(filenames can have multiple .'s

table_name是表的名称

这篇关于SQLite:如何选择字符串的一部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！