Re: please need help: alpha numeric sorting - Mailing list pgsql-novice

On Fri, 2002-08-02 at 09:48, Raouf wrote:
> Hi Gurus,
>
> I need your help for this one:
>
> let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
>
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.2 cisco ccnp remote access
> 12.11cisco ccnp switching
> 12.3 cisco ccnp support
>
>
> I'd like to sort that column in alphanumeric order, like this:
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.2 cisco ccnp remote access
> 12.3 cisco ccnp support
> 12.11cisco ccnp switching
>
>
> but if I sort using group by title and order by title asc I have:
>
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.11cisco ccnp switching
> 12.2 cisco ccnp remote access
> 12.3 cisco ccnp support
> 12.11cisco ccnp switching
>
> where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
>
> Is it possible  ?
>
> thanks for your help

It is possible (hack at bottom)...but I certainly wouldn't recommend it
because as you get more than 10 rows, you'll *really* see a slow down of
the function.  indexes won't apply, and sorts will be significantly
slowed.

I'd recommend that you have two more columns 'major' and 'minor', and
have these two columns be integers, like this:

drop table a;
create table a (
  major int4,
  minor int4,
  description varchar(255)
);

Now, when inserting into this table, parse the numbers once using your
favorite language, or use a stored function to parse it:
insert into a values(12, 1,'12.1 description1');
insert into a values(12, 2,'12.2 description1');
insert into a values(12,10,'12.10description1');

Now, selecting with an appropriate order is a breeze:

select description from a
order by major, minor;

That way you can have indexes on both major and minor, and your sorts
will be much faster and far more scalable.

For fun, here's the hack that will let you do thwat you wanted in the
first place...(blech!)

HACK FOLLOWS
------------

-- First, create a test bed:
drop table a;
create table a (b varchar(255));
insert into a values ('12.1 testing');
insert into a values ('12.2 testing');
insert into a values ('12.10testing');

-- Now, see if we still have this problem...
select * from a order by b;

-- Okay, now convert the leading number (12.1) into '12.001', so
-- we can then sort numerically, and do so.  Yuck.
select
  *
from a
order by
  -- break out the digits before the '.'
  substring(
     substring(b for 5)
     for position('.' in substring(b for 5))-1)
  -- add the '.' back
  ||'.'||
  -- Turn that back to a string
  to_char(
    -- Turn them to a zero padded number.
    to_number(
      -- Get the digits after the '.'
      substring(
       substring(b for 5)
       from position('.' in substring(b for 5))+1)
      ,'999')
    ,'FM000');

-Ken