Re: SQL : Order by for varchar ???

From: <Diana_Duncan_at_ttpartners.com>
Date: Thu, 07 Jun 2001 12:34:30 -0700
Message-ID: <F001.00320D6F.20010607114131@fatcity.com>

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).

Received on Thu Jun 07 2001 - 14:34:30 CDT