Is there a way to replace a NUL that got into a string in SQLite?-CodePudding

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.

CodePudding user response：

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>