Re: How to sort these values 'correctly?' 1.1..1, 1.1..2, 1.1..13

On Thursday 11 July 2002 12:46, Jay Weinshenker wrote:
> So imagine I have this series of numbers
> 1.1
> 1.1..1
> 1.1..13
> 1.1..2
> 1.1..3
> and I want them to sort like a human would sort them
>
> 1.1
> 1.1..1
> 1.1..2
> 1.1..3
> 1.1..7
> 1.1..13
> Anyone have sql to accomplish this?

Here ya go.

You will need to login as SYS and
'grant execute on owa_pattern to the_owner_of_the_function' for this to work.

Jared

drop table t;

create table t(t varchar2(20));

insert into t(t) values('1.1');
insert into t(t) values('1.1..1');
insert into t(t) values('1.1..2');
insert into t(t) values('1.1..3');
insert into t(t) values('1.1..7');
insert into t(t) values('1.1..13');

commit;

create or replace function sortable( data_in varchar2 ) return number
as

   sort_data varchar2(40);
   zero_pad varchar2(5) := '00000';
begin

   sort_data := data_in;
   owa_pattern.change(sort_data,'\.+','\.','g');    owa_pattern.change(sort_data,'\.+',zero_pad,'g');    sort_data := sort_data || zero_pad;

   return to_number(sort_data);

end;
/

show error function sortable
select t
from t
order by sortable(t)
/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).