On Thu, Apr 19, 2018 at 1:49 PM, David Raymond <david.raym...@tomtom.com> wrote:
> 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. > > length has a note about terminating at nul. I do know it's not an issue with the SQL standard; only implementations of the standard. The SQL standard is quite clear... As mentioned here... http://sqlite.1065341.n5.nabble.com/sqlite-command-line-tool-fails-to-dump-data-tp100196p100218.html http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt spaces are used to separate syntactic elements. Multiple spaces and line breaks are treated as a single space. Apart from those symbols to which special functions were given above, *other characters and character strings in a formula stand for themselves. In *addition, if the symbols to the right of the definition operator in a produc- tion consist entirely of BNF symbols, then those symbols stand for themselves and do not take on their special meaning. For every portion of the string enclosed in square brackets, either delete the brackets and their contents or change the brackets to braces. (from SQL 92) By this, I shouldn't also be able to use ~, `, Γειά σου Κόσμ, Привет мир, or any other UNICODE character. (that is if you say things not listed are " cannot contain embedded <unlisted characters>;" There is no specification that \0 means anything (in SQL). 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
