Things I Learned (SQLite)

String Manipulation in SQLite🔗

SQLite

在 SQLite 中,如果需要对列表的字符串数据做一些简单的变化,可以直接通过 SQLite 内建的函数来完成,而不需要借助外部的程序语言(如 Node.js)。使用内建的操作,转化的效率会远高于使用外部的语言来进行操作。下面通过一些例子来简单介绍一些和字符串相关的操作方法:

字符串截取

可以使用 SQLite 自带的 substr 的函数来截取字符串。函数的签名是 substr(string, start, length),其中 startlength 可以是负数,具体的行为可以参考这里的介绍。

假设有一列图像文件相关的数据:

xxx.jpg
yyy.gif
zzz.png

想要统计文件的格式,一个简单的写法如下(不考虑 .jpeg 之类的情况):

select substr(image_column_name, -3) as suffix from table_name group by suffix;

需要注意的一点是,SQLite 中的 substr 函数,记录的 start 下标,是从 1 而不是 0 开始的。

查找字符

在上例中,如果需要考虑 .jpeg 之类的情况,直接写死起始数字的下标就显得不太合适了。这时候,可以使用 instr 来配合查找:

select substr(column, instr(column, '.') + 1) as suffix from table_name;

instr 的文档可以看这里

字符串长度

如果需要删除字符串的最后几位,光有 substr 函数就不够用了,还需要知道一个字符串具体的长度,才能确定需要截取的字符串长度是多少(定长字符串除外)。这就需要 SQLite 自带的 length 函数了。细节可以参见文档,以下举一个实际的例子。

假设有一列身高相关的数据:

170cm
168cm
182cm

想要截取其中数字的部分,可以使用 substrlength 配合着这么写:

select substr(column, 1, length(column) - 2) from table;

字符串转数字

接着上文的例子,如果希望进一步把字符串转化成数字,可以使用 cast 函数:

select cast('170' as integer);

结合起来:

select cast(substr(column, 1, length(column) - 2) as integer) from table;

注意,这里 substrcask 函数在处理 NULL 的时候,都是不会做任何操作,直接返回 NULL 的。因此,如果上述的列中有数据是 NULL 而不是字符串,使用 cask + substr 的操作也会得到 NULL 的结果,不会有报错或其他问题。

转化成 NULL

然而在上例中,如果 cask 收到的参数是空字符串,那么转换的结果就是 0。这就不一定符合需求了。可以使用 nullif 这个操作符,将这种情况强制转化成 NULL

select nullif(column, '');

获取 ASCII 码

使用字符串存储的成本会比使用整数来的大一些。除了上面提到的将字符串直接转化成数字的例子,对于一些单个字符类型的值,转化成数字存储也不失为一个好方案。比如,将某组 AZ 的字母转化成 025 的数字。这时候,就可以使用 unicode 函数了:

select unicode(column) - 65 from table_name;

这里 65A 的 ASCII 码。