SQLite 数据清洗

2022-10-19

SQLite 导入大量数据的时候，可能会存在数据质量问题，例如字段前后有空格、回车换行，或者值为 NULL 等问题，这时就需要对这些问题数据进行清洗。本文将会结合真实的案例将数据清洗过程中用到的 SQL 罗列出来。

1. 重置空值

表中部分为 NULL 的数据如果需要重置成默认值或空字符串的时候，可以使用下面的语句，这里需要注意空值的判断条件。

1	UPDATE [Table] SET [Column]='' WHERE [Column] IS NULL

如果要将表中的多个为 NULL 的字段批量重置成默认值或空字符串的时候，可以使用下面的语句。

UPDATE [Table] SET 
[Column1]=IFNULL([Column1], ''),
[Column2]=IFNULL([Column2], ''),
[Column3]=IFNULL([Column3], ''),
[Column4]=IFNULL([Column4], '');

2. 去除首尾

SQLite 语句是不能直接识别回车、换行、空格等特殊字符的，但却可以识别这些字符的十六进制表示。所以，想去除字段首尾的这些字符，就需要以这些字符的十六进制来表示。表格如下：

序号	十六进制	缩写/字符	描述
1	0x0A	LF (NL line feed, new line)	换行符
2	0x0D	CR (carriage return)	回车符
3	0x20	(space)	空格符
4	0x2F	/	斜杠

以下两图分别为字段的前后有换行符、空格的情况。为了明确表达字段的前后内容，这里特意在内容查看器里将整个字段内容都标记了出来。

此处输入图片的描述

执行如下 SQL 语句，继续用内容查看器查看修改后的内容，会发现字段首位的换行符、空格都被清除掉了。

1 2	UPDATE [Table] SET [Column]=TRIM([Column], x'0A') //　去除换行 UPDATE [Table] SET [Column]=TRIM([Column], x'20') //　去除空格

此处输入图片的描述

3. 替换字符

还有部分格式不一致的数据。例如，字段中的分隔符同时存在逗号和连接符，如果想将其统一成连接符该怎么操作呢？可以尝试下 REPLACE 语法将字段内的逗号替换为连接符。语句如下；

1	UPDATE [Table] SET [Column]=REPLACE([Column],',','-')

此处输入图片的描述