如何删除sqlite文本字段中的回车符? [英] How to remove carriage returns in a text field in sqlite?

问题描述

我有一个包含超过 40 万条记录的 sqlite 数据库.我刚刚发现某些文本字段中有回车符，我想清除它们.我想复制原始表的结构，然后执行以下操作:

I have an sqlite database with over 400k records. I have just found that some of the text fields have carriage returns in them and I wanted to clean them out. I wanted to copy the structure of the original table and then do something like:

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

其中 XXX 是回车的任何代码.它不是 \n.但我无法找出它是什么.

Where XXX is whatever the code would be for a carriage return. It's not \n. But I can't find out what it is.

推荐答案

SQLite 允许您在字符串文字中放置换行符，如下所示:

SQLite lets you put line breaks inside string literals, like this:

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

如果您不喜欢这种语法，可以将字符串作为 BLOB 传递:X'0D' for \r 或X'0A' 表示 \n(假设默认 UTF-8 编码).

If you don't like this syntax, you can pass the string as a BLOB: X'0D' for \r or X'0A' for \n (assuming the default UTF-8 encoding).

由于最初编写此答案，SQLite 添加了 CHAR 函数.因此，您现在可以为 \r 编写 CHAR(13) 或为 \n 编写 CHAR(10)，即无论您的数据库是用 UTF-8 还是 UTF-16 编码，都可以使用.

Since this answer was originally written, SQLite has added a CHAR function. So you can now write CHAR(13) for \r or CHAR(10) for \n, which will work whether your database is encoded in UTF-8 or UTF-16.

这篇关于如何删除sqlite文本字段中的回车符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！