How to remove carriage return to textbox in sqlite?

Question

I have a sqlite database with more than 400 thousand records. I just discovered that some text boxes have carriages, and I wanted to clear them. I wanted to copy the structure of the source 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 the code that will be used to return the carriage. It is not \n . But I can’t understand what it is.

Answer 1

SQLite allows you to put line breaks inside string literals, for example:

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

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

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

Answer 2

From @MarkCarter's comment on the question above:

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