Re: What query can sort this?

From: andrewst <member14183_at_dbforums.com>
Date: Thu, 04 Sep 2003 09:06:12 -0400
Message-ID: <3329599.1062680772@dbforums.com>

Originally posted by Sean

> I have been trying to sort on a column of data and am stumped. Does

> anyone have any advice?

>

> The column is a VARCHAR2(15)

>

> Footnote_no

> ------------

> 1

> 1.1

> 1.2

> 1.2.1

> 1.2.2

> 1.2.2.1

> 1.4

> 1.6

> 1.10

> 1.11

> 1.12

> 1.12.1

> 1.12.1.1

>

> etc.

>

> As you can tell from the data above, it is already in a nice ascending

> order and is the ultimate result I am looking for. By just applying a

> sort you get

>

> 1.10

> 1.11

> 1.12

> 1

> etc...

>

> I think you get the idea.

>

> Any suggestions?

> Thanks,

> Sean

If you have a utility for parsing a delimited string (a useful utility), you can do something like this:

create or replace
function convcode( code in varchar2 ) return varchar2 is

  v_table parse.varchar2_table;
  v_nfields integer;
  v_retval varchar2(1000);

begin
  parse.delimstring_to_table( code, v_table, v_nfields, '.' );   for i in 1..v_nfields loop
    v_retval := v_retval || lpad( v_table(i), 4, '0' );   end loop;
  return v_retval;
end;

Then:

  1 select code, convcode(code) as convcode

  2 from junk

  3* order by convcode(code);

CODE                 CONVCODE

-------------------- ------------------------------

1                    0001

1.1                  00010001

1.2.2.1              0001000200020001

1.11                 00010011

1.12                 00010012

1.12.1.1             0001001200010001

--
Posted via http://dbforums.com

Received on Thu Sep 04 2003 - 08:06:12 CDT