After a little testing, of the core functions: Affected by \x00: substr like length quote replace when you're trying to replace the \x00
Not affected by \x00: plain ol select instr lower upper trim ltrim rtrim replace when you're not replacing the \x00 (works and replaces bits after the \x00 as well) || At the moment I can't find anything in the documentation that covers this. So instead of like maybe you could use instr() instead? And it looks like messing with cast might do it to make substr work select cast(substr(cast(Data as blob), 11, 5) as text) from Record; select Data from Record where instr(Data, 'binar'); Seems a little odd, as even when I declare the Data field as blob to begin with substr still doesn't work, but calling on the cast value does. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of MARCHAND Loïc Sent: Thursday, April 19, 2018 8:27 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3 - Search on text field with \0 binary data I index a file in a SQLite DB. I create my table with this: CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID)) I read a file, and for each line I add a row on the table. Each line can have binary data at end. It's not a problem for many chars, but \0 char make a problem. If I have a line like this : "My data \0with binary". When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5) FROM Record return an empty string or SELECT substr(Data, 4, 10) FROM Record return data) When I try to search a data (SELECT Data FROM Record WHERE Data LIKE '%binar%') return 0 rows returned. How can I solve this problem ? I try to replace \0 by an other char sequence, but it's not a good idea because can I have this sequence in my file. Thank you Loïc _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
