Re: order by text

From: Peter Nilsson <airia_at_acay.com.au>
Date: Wed, 13 Feb 2008 20:06:59 -0800 (PST)
Message-ID: <3a1fc22a-2979-427f-886e-e094a14ab8a3@l16g2000hsh.googlegroups.com>

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.

I think you need to give a clearer example. Are you sorting by number, then by text, or vice versa?

> Is there a way to fix this?

I had a similar problem sorting legislative act sections. I used regular expressions (10g)...

  with
    blah as
    (

      select to_char(level) x from dual
        connect by level <= 10 union all
      select to_char(level) || 'A' from dual
        connect by level between 2 and 5 union all
      select to_char(level) || 'B' from dual
        connect by level between 4 and 5

    )
  select x
  from blah
  order by
    to_number(regexp_substr(x, '[0-9]+')),     lower(x) nulls first;

To do a text comparison in the absense of the number, you can use regexp_replace(x, '[0-9]+', null) to extract the text component. [That's also what you'll need if you allow 9a and 009a say in your normal ids and you want them to compare equal. The simple second ordering by x, or lower(x) in the sample above, will fail.]

You say that there are always digits, but you can also compare cases where they may not be a digit if you know the digits (if present) will be always be prefix, or always suffix...

• prefix to_number(to_number(regexp_substr('0' || x, '[0-9]+')))
• suffix to_number(to_number(regexp_substr(x || '0', '[0-9]+'))) / 10

--
Peter

Received on Wed Feb 13 2008 - 22:06:59 CST