CODEWITHSUNDEEP
sqlitecarriage-return
alj
alj

Reputation: 3039

How to remove carriage returns in a text field in 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

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.

Upvotes: 18

Views: 21417

Answers (3)

Erik Bachmann
Erik Bachmann

Reputation: 163

If you operate in a mixed environment with Windows/Linux/Mac it might be a safer solution to do a two step convertion:

SELECT replace( replace( dirty_text_field, CHAR(10), "<BR>"), CHAR(13), "") 
FROM temp_config;

This will work with most combinations of '\n' and '\r'

Upvotes: 3

aculich
aculich

Reputation: 14865

From @MarkCarter's comment on the question above:

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

Upvotes: 9

dan04
dan04

Reputation: 91207

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.

Upvotes: 36

Related Questions