Re: a sorting conundrum

On Fri, 4 Mar 2005 06:24:29 +1100, Dimitre Novatchev
<dnovatchev@xxxxxxxxx> wrote:
> Use the following sorting key:
> 
> substring(translate(., ' ', ''),
>                 string-length(substring-before(.,' '))  + 1
>                 )
> 
> This calculates the index of the first space, then removes the
> space(s) from the string, then uses what follows the space as the
> sorting key.
> 
> Seems to be more logical than any hack. Tested.


If we want to have a more efficient variant of the above, which avoids
the translate(),
(and we assume an intelligent optimizer),

then the following should be much more efficient:


substring(., 
                  string-length(substring-before(.,' ')) 
                + 
                  (string-length(substring-before(.,' ')) > 0)
                 )


Cheers,
Dimitre