Subject Re: ORDER BY on large VARCHAR columns
Author skotaylor
--- In, "robert_difalco"
<rdifalco@t...> wrote:
> > have you tried adding another field of say 128 characters (I don't
> know your
> > collation), on insert/update put the leading substring in this
> field and
> > have this field indexed so you use it to return the order you want?
> > Alan
> Problem is that the string would be too short to be valuable in an
> ORDER BY. Consider that this field is something like a qualified
> file system path. The front part could be repeated in many of the
> rows but you need both the front and the back (and even the middle)
> for ordering them accurately. I think the best approach is to
> calculate the order as they are inserted, just would be nice to have
> an example of how best to do that.

How about an algorithm to shorten the filename to 8.3 format, like
Win95 used to, then index that to point to the real path?
ie: C:\My Documents\Crappy Long Filename\Stupid Long Path\That Some
Users Insist\On Creating for Lack\Of imagination

Might be tough to sort those, but you could using the integer after
the tilde. When you run short (past nine) the tilde moves back one
and you get C:\MYDOC~10 and on.

The algorithm part is up to you. Java should be able to do it with a
nicely designed RegEx.

If not, at least it looks funny. ;0)

Good luck.