Ooh, a fun one. If you are sure of the format of the data (as you'll see
in the following function) you can create a function to make the data
numeric. I can't think of another way to do it, but maybe someone else
can?...
create or replace function fractionToDecimal (str in varchar2) return
number is
fract varchar2(100);
dec number;
begin
-- This function takes numbers in the form below and returns a decimal
number
-- [whole-]numerator[/denominator]["|']
-- For example:
-- 5/8"
-- 1-1/2"
-- 4'
--
-- Remove the symbol.
--
fract := rtrim(str, '"''');
-- Replace occurences of '-' with '+'
--
fract := replace(fract, '-', '+');
-- Evaluate the resulting expression
--
execute immediate 'select ' || fract || ' from dual' into dec;
return dec;
end fractionToDecimal;
/
1 select width, fractionToDecimal(width) from test
2* order by 2 desc
SQL> /
WIDTH FRACTIONTODECIMAL(WIDTH)
-------------------- ------------------------
4" 4
2" 2
1-1/2" 1.5
1" 1
3/4" .75
5/8" .625
6 rows selected.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
"Apps Sol"
<apps_sol@hot To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
mail.com> cc:
Sent by: Fax to:
root@fatcity. Subject: SQL : Order by for varchar ???
com
06/07/2001
01:56 PM
Please
respond to
ORACLE-L
One of our developers was looking for solution for his problem ..
Any idea folks .. ??
He wants to sort his data asc or desc for a varchar column ..
1> create table test(width varchar2(20));
2>
insert into test values('5/8"');
insert into test values('4"');
insert into test values('3/4"');
insert into test values('2"');
insert into test values('1-1/2"');
insert into test values('1"');
3>
select width from test order by width DESC;
5/8"
3/4"
1"
1-1/2"
2"
4"
Cheers
RK
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Apps Sol
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
