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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
