| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL : Order by for varchar ???
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: Diana_Duncan_at_ttpartners.com
"Apps Sol"
<apps_sol_at_hot To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
mail.com> cc:
Sent by: Fax to:
root_at_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
>
> 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: Diana_Duncan_at_ttpartners.com
>
>
>
> "Apps Sol"
> <apps_sol_at_hot To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> mail.com> cc:
> Sent by: Fax to:
> root_at_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: apps_sol_at_hotmail.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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Diana_Duncan_at_ttpartners.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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Apps Sol INET: apps_sol_at_hotmail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Diana_Duncan_at_ttpartners.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).Received on Fri Jun 08 2001 - 11:17:36 CDT
 |
 |