Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to sort numbers in varchar2

Re: how to sort numbers in varchar2

From: Jim Kennedy <kennedy-down_with_spammers_at_comcast.net>
Date: Wed, 09 Jul 2003 14:08:38 GMT
Message-ID: <GlVOa.14224$OZ2.2938@rwcrnsc54>


wrap the to_number in a function that if an error is raised it will return something reasonable.(eg -99999999999999) depending on your data. Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Jan" <janik_at_pobox.sk> wrote in message
news:81511301.0307082233.6820b46c_at_posting.google.com...

> Yours has also one big drawback,
>
> Original problem was:
>
> "I have a varchar2 column which stores characters as well as numbers in
> string such as '12345'. "
>
> so if you do TO_NUMBER(col1) and there is a text it will raise an error.
>
>
> Kenneth Koenraadt wrote in message
news:<3f0ad068.448094_at_news.inet.tele.dk>...
> > On Tue, 8 Jul 2003 13:09:46 GMT, Brian Peasland
> > <oracle_dba_at_remove_spam.peasland.com> wrote:
> >
> > >I tricked the system into doing this for me by ordering by the size of
> > >the column and then by the data in the column. For instance, assume I
> > >have TABLE_A as follows:
> > >
> > >COL_A
> > >--------------------
> > >123
> > >45
> > >532
> > >133
> > >
> > >Now, I can "order" these by doing the following:
> > >
> > >ORA9I SQL> select col_a,length(col_a)
> > > 2 from table_a
> > > 3 order by length(col_a),col_a;
> > >
> > >COL_A LENGTH(COL_A)
> > >-------------------- -------------
> > >45 2
> > >123 3
> > >133 3
> > >532 3
> > >
> > >Then, I just never displayed the second column in my application.
> > >
> > >Of course, doing the proper thing and storing the numbers in a number
> > >field would be better....
> >
> > Your solution has one drawback : It can only handle integers. Instead,
> > just :
> >
> > select col_a from table_a order by to_number(col_a);
> >
> > handles decimals as well.
> >
> >
> > - Kenneth Koenraadt
Received on Wed Jul 09 2003 - 09:08:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US