Re: order by text

On Feb 13, 9:23 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Feb 13, 9:49 pm, C-man <c00ldia..._at_gmail.com> wrote:
>
> > Hi,
>
> > I have a variable field of type varchar2 called storeId. I need to
> > sort the store Id's but they are of variable length and some of them
> > contain characters and digits while others contain only digits. I
> > tried using order by lpad(storeId, 10) asc but it does not work in the
> > case that 1 id is a11 and another one is b2. It gives me b2 and then
> > a11.
>
> > Is there a way to fix this?
>
> > Thank you
>
> Assuming you are using an ASCII based character set, space comes
> before the letters so ' b2' comes before 'a11' (even before 'b11').
>
> Try RPAD() ?
>
> HTH,
> ed

rpad will work in that case but for cases when the ids are 123, 12, 22, 223 and 4, rpad won't help. I am trying to do something similar to the sort provided in excel. Received on Thu Feb 14 2008 - 08:41:16 CST