How to remove carriage returns in a text field in sqlite?

Question

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.

Answer 1

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.