--- In firebird-support@yahoogroups.com, "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
becomes:
C:\MYDOCU~1\CRAPPY~1\STUPID~1\THATSO~1\ONCREA~1\OFIMAG~1

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.