On Fri, 26 Apr 2019 10:36:34 -0700 Jens Alfke <j...@mooseyard.com> wrote:
> The only workaround I can think of is to define a custom function > that converts a blob to a string and collate using those strings ? > e.g. `? ORDER BY collatable_blob(b)`. But this requires expensive > string conversions, Your UDF need not convert to a string. It could return an integer or, if that's not enough, couldn't it return a different blob whose bit order yields the correct ordering? > and it doesn?t work well with indexes. If indexing is important, that's a clue that the blob is not your friend to start with. It might be worthwhile to store the searchable components in "redundant" columns that can be readily indexed. By analogy, if your blob is a PDF, it might be convenient to store the author and title in columns for search & display purposes. If you write UDFs to pull out the pieces, you could invoke them in a trigger to keep the "redundant" columns consistent. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users