On Thu, May 23, 2019 at 12:37 PM Simon Slavin <slav...@bigfraud.org> wrote:
> On 23 May 2019, at 3:55am, Keith Medcalf <kmedc...@dessus.com> wrote: > > Technically, COLLATE only works on TEXT. Most people declare their own > types as binary blobs and the programmer has to keep track of what is in > there and how to work with it. > > So it would seem that rather than define a function which turns a value of > my type into a BLOB, it would be better to create one which turns it into > some sort of normalised text form. Then I can create whatever COLLATEs I > need to do my specialised sorting and handling. > > IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of > the four parts is always three digits long. Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual. You'd better have a CHECK constraint to enforce your format. Otherwise lexical ordering won't work with non-expected (but more natural) 1.1.1.1. While writing a custom collation (the so called "natural ordering") would work for either format. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
