String Manipulation in SQLite🔗

2019-08-11 • SQLite • by LaySent

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

字符串截取

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

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

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

想要截取其中数字的部分，可以使用 substr 和 length 配合着这么写：

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;

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

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

select nullif(column, '');

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

select unicode(column) - 65 from table_name;

这里 65 是 A 的 ASCII 码。