I have a Sqlite db that has gotten a NUL byte in some strings. I would like to replace that NUL byte with '\0' so that I know that string contained a NUL byte and everything following the NUL byte is visible. This is a db that I inherited from work. I didn't make this mess, I just have to deal with it.
I tried something like:
iif(instr(Message, char(0)) = 0
, Message
, substr(Message, 1, instr(Message, char(0)))
|| "\0"
|| substr(Message, instr(Message, char(0))-length(Message)+1)
)
but it would seem that I can't actually access the string beyond the NUL.
Looks like substr and length will treat the string as NUL terminated and doesn't go to the end of the actual string. I can get the length of the actual string in bytes by using LENGTH(CAST(Message AS BLOB)) (which is fine as we're using only the 7 bit characters in UTF8), but that doesn't deal with substr which doesn't go past the NUL (forwards (+ve start position) or backwards (-ve start position)).
Is there some way around this?
BTW, I am aware that the NUL can be stripped, but that removes everything after the NUL. That information can be found here. It's from there where I got the idea above from.
I've also tried:
replace(Message, char(0), "\0")
which also didn't work.
You need to use substr() to get the part before the 0 byte, and again to get the part after - but that only works with blobs, not text, so a cast is required:
sqlite> CREATE TABLE foo(bar);
sqlite> INSERT INTO foo VALUES ('apple' || char(0) || 'bacon');
sqlite> SELECT * FROM foo;
bar
-----
apple
sqlite> SELECT length(CAST(bar AS BLOB)) FROM foo;
length(CAST(bar AS BLOB))
-------------------------
11
sqlite> SELECT substr(bar, 1, instr(bar, char(0)) - 1) || '\0' || substr(CAST(bar AS blob), instr(bar, char(0)) + 1) AS bar FROM foo;
bar
------------
apple\0bacon
sqlite>