如何在sqlite中删除文本字段中的换行符？

Question

我有一个包含超过400k条记录的SQLite数据库。我刚刚发现一些文本字段中有回车符，我想将它们清除掉。我想复制原始表的结构，然后做以下操作：

INSERT INTO large_table_copy 
SELECT date, other_fields, replace(dirty_text_field,XXX,"") 
FROM large_table

其中XXX是回车符的代码，但不是\n。但我找不到它的代号。

- alj

Answer 1

SQLite允许在字符串文字中插入换行符，例如：

SELECT replace(dirty_text_field, '
', '');

如果你不喜欢这种语法，你可以将字符串作为BLOB传递: X'0D'代表\r或X'0A'代表\n（假设默认编码为UTF-8）。

编辑:自从这个答案最初被写出来以后，SQLite已经添加了一个CHAR函数。因此，现在你可以写CHAR(13)代表\r或CHAR(10)代表\n，这将适用于无论你的数据库是否编码为UTF-8或UTF-16。

- dan04

---

对于那些不清楚的人...你可以像这样做 SELECT replace(dirty_text_field, X'0A', '\n')... 来将单个 \n 字符替换为等效的 "\n" 2个字符字符串。 - Mark

@MarkCarter 这是一个很好的答案，为什么不把它发布为回答而不仅仅是评论呢？ - aculich

@aculich 因为它在答案中没有提供任何根本性的新东西。 - Mark

我不认为这在现今能够工作。sqlite> SELECT replace(whitelist.wl_date, CHAR(13), ''); 导致 Error: no such column: whitelist.wl_date。我已经尝试使用数据库名称作为前缀，以及省略表名。 - jww

Answer 2

根据 @MarkCarter 在上面的问题评论中所说：

SELECT replace(dirty_text_field, X'0A', '\n');

- aculich

Answer 3

如果您在Windows/Linux/Mac混合环境中操作，进行两步转换可能是更安全的解决方案：

SELECT replace( replace( dirty_text_field, CHAR(10), "<BR>"), CHAR(13), "") 
FROM temp_config;

这将适用于大多数 '\n' 和 '\r' 的组合。

- Erik Bachmann