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
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 lets you put line breaks inside string literals, like this:
SELECT replace(dirty_text_field, '
', '');
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).
Edit: 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.
From @MarkCarter's comment on the question above:
SELECT replace(dirty_text_field, X'0A', '\n');
精彩评论