I'd guess there are characters in the data that weren't in your previously
posted sample. As I said, the function as written will only work with
widths in the format of [whole-]numerator[/denominator]["|']
If you have any other formats, you'll have to modify the function to take
care of them.
HTH,
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: Re: SQL : Order by for varchar
???
com
06/08/2001
12:01 PM
Please
respond to
ORACLE-L
Looks like I am missing something..
this is the error we get
select width, fractionToDecimal(width) from test
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at "PS09.FRACTIONTODECIMAL", line 12
ORA-06512: at line 1
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, June 07, 2001 3:41 PM
>
> 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).
>
--
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).
