Roger Andersson wrote: > On 11/09/11 19:42, Yuriy Kaminskiy wrote: >> Paul Corke wrote: >>> On 09 November 2011 15:32, hmas wrote: >>> >>>> sqlite> select hex(foocol) from footable where foocol like >>>> '999998012470700566'; >>>> 39393939393830313234373037303035363600 >>> It looks like there's an extra 00 on the end. >>> >>> x'3900' != x'39' >> That said, it seems LIKE operator is buggy. >> sqlite> SELECT X'1245005679' LIKE X'1245001234'; >> 1 >> > > On Windows > > sqlite3.exe > SQLite version 3.7.9 2011-11-01 00:52:41 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> SELECT X'1245005679',X'1245001234'; > ↕E|↕E
These strings are (intentionally) not printable, no surprise here. Look at: SELECT hex(X'1245005679'),hex(X'1245001234'); And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679' > X'1245001234'; 1 -- correct "LIKE" (in both "native" and "icu" implementations) ignores value length (sqlite_value_bytes) and stops at NUL character. Compare that with "=" and ">" that compares full value. Arguable SQL_BLOB should be illegal operand for LIKE/GLOB, but then this should be documented and they should return error when called with BLOB argument instead of returning nonsense. Well, not sure, maybe shell.c is also buggy in respect with printing blobs (only ".mode insert" handles SQLITE_BLOB properly [IIRC, NUL-in-middle is only legal in BLOB], other .mode's treats everything as text and ignores length, and so arguable buggy), but that's different issue. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
