Re: order by text

From: Ed Prochak <edprochak_at_gmail.com>
Date: Wed, 13 Feb 2008 18:23:16 -0800 (PST)
Message-ID: <12378148-18eb-4019-9253-7dfab12dc738@e6g2000prf.googlegroups.com>


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 Received on Wed Feb 13 2008 - 20:23:16 CST

Original text of this message