Finding and removing carriage returns in your SQLite table

The problem with copy data from e.g., an excel or calc sheet into an SQLite database (or any database I reckon) is that the data you copy might include an carriage return. I found this out the hard way after having copied data from a spreadsheet into a attribute table of a GRASS GIS vector layer (which is stored in a SQLite database). When later I opened the table with the Attribute table manager in GRASS GIS, I got a lot of error messages like the one in the screenshot below:

Screenshot error message from the GRASS GIS Attribute table manager

Screenshot error message from the GRASS GIS Attribute table manager

Going to the mentioned records (253 in this case) I found out that they all contained carriage returns in the just updated fields. A bit of googling learned that it is easy enough to select all records with carriage returns; you just include a carriage return in your code, e.g.:

SELECT *
FROM table
WHERE column LIKE '%
%'

Knowing this, it is easy to clean up your table by replacing the values. The probably easiest way is with the UPDATE function:

UPDATE table
SET column=REPLACE(column,'
', '')

I didn’t try to do this on the command line, but you probably need to use the hexadecimal representations of the carriage return, something in the line of:

REPLACE(field1, 0x0A,'')

I tried to use the hex notation instead of an actual carriage return in my SQLite manager, but it didn’t work. So if you know how to use the hex code, let me know.

About these ads

3 thoughts on “Finding and removing carriage returns in your SQLite table

  1. Pingback: Finding and removing carriage returns in your SQLite | GIS Tutorial

  2. Martin Schneeweis

    I found a solution at

    http://sqlite.1065341.n5.nabble.com/replacing-all-newlines-in-a-field-td33024.html

    and

    http://stackoverflow.com/questions/4642535/how-to-remove-carriage-returns-in-a-text-field-in-sqlite

    Linux-New-Line (LF):
    > select replace(field_containing_new_line, x’0A’, ‘ // ‘) from table_containing_field

    Windows-New-Line (CR-LF) should be:
    > select replace(field_containing_new_line, x’0D0A’, ‘ // ‘) from table_containing_field

    Where ” // ” is the replacement-string for the new line.

    Reply
  3. Robert

    Thanks a bunch. I actually copied data between MySQL and Sqlite. The export contained ‘\r\n’ and I needed to translate that into the Sqlite equivalent.
    Update table set column=REPLACE(column, ‘\r\n’, ‘
    ‘);

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s