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

sqlite carriage-return
alj picture alj · Jan 10, 2011 · Viewed 14.9k times · Source

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

dan04 picture dan04 · Jan 10, 2011

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.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master …

Read More