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.
>Solution :
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));