Re: SQL : Order by for varchar ???

Apps Sol wrote:
>
> 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

Are your developers masochist? I think that you have two ways out :

1. Write a stored function of death which evaluates expressions and returns a number, which could be used in the order by.
2. Store the values as numbers (float), and write a function which, for display purpose, returns them in the format given above (although I don't see how make 1-1/2" not appear as 1/2"). This is likely to be easier to do (given a float value p, finding the smaller integer n such as p * n is an integer, or in other words is equal to trunc(p * n), then returning to_char(p * n) || '/' || ltrim(to_char(n)) || '"'; you can then quietly order by your number.

Guess which one I prefer. The problem of course is how data is entered, which means that in practice you may find difficult to avoid 1). Now, if it's all p/n or p kinds of strings, it's relatively easy to do with a pinch of instr(), one cup of substr() and two of to_number().  

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).