Re: order by text

On Feb 14, 9:41 am, C-man <c00ldia..._at_gmail.com> wrote:
> 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.

That is how text collation works. Peter's suggestion of regular expressions should help. Basically create a function that takes a storeId and returns a value that sorts the row into the proper place.

I cannot access Oracle today so I cannot provide an example. But I would think a function that pads left with zeroes for pure numbers and pads right for values with leading letters. Where other cases (123a) belong is up to you.

Ed Received on Mon Feb 18 2008 - 06:31:47 CST