On Thu, May 23, 2019 at 12:37 PM Simon Slavin <[email protected]> wrote:
> On 23 May 2019, at 3:55am, Keith Medcalf <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
