How to select rows with \u0000 (null character) in a varchar column in SQLite?

Question

Long story short, lots of bogus data entered the SQLite database over time, where there is valid data before a null character in a varchar column - and then bogus data afterwards. I'm using \u0000 to represent the null character here.

validData\u0000bogusData

I need to clean the dataset so that the data after null character (and the null character) is gone. Unfortunately the data doesn't fit a pattern so only way is to look for the null character.

I've been trying to query the bad data so that I can clean it up.

SQL that I have tried:

SELECT id FROM table WHERE field LIKE "%" || CHAR(0) || "%"

Result: This returns all rows in the table. I need just the rows with null character in them.

String sql = "SELECT id FROM table WHERE field LIKE \"%\u0000%\"";

Result: SQLite stops processing SQL string when it encounters null char \u0000, Error: SQL syntax error near LIKE "%

Any help is appreciated, all I could find was info on getting rows with NULL value.

Answer 1

Use INSTR() to get the rows which contain CHAR(0):

SELECT * FROM tablename WHERE INSTR(field, CHAR(0));

and if you want you can update the table to remove the bogus data:

UPDATE tablename
SET field = SUBSTR(field, 1, INSTR(field, CHAR(0)) - 1)
WHERE INSTR(field, CHAR(0));