Re: VARCHAR to NUMBER

Hi!

>select r.page
>from readerresponse_complete r
>order by
to_number(translate(r.page,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKL MNOPQRSTUVWXYZ', '0123456789')) asc

-------What if you add:
, translate
(r.page,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')

to get the non-numeric characteres?

Unless uppercase/lowercase is a important sorting issue, use UPPER on r.page to avoid repeating the alphabet.

A bit shorter, but equally cryptical perhaps? is ....(with or without UPPER.-)
ORDER BY LPAD(UPPER(r.page), 4, ' ') asc ;

Frank

jun zhang <junzhang_at_bu.edu> wrote in message news:96ef5j$puk$1_at_news3.bu.edu...
>
> Hi,
>
> I have a very similar question,
>
> in Oracle I am using sql like:
>
> select r.page
> from readerresponse_complete r
> order by to_number(translate
(r.page,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')) asc
>
> It works partially, for page 1a, 2b, it sort fine. But no way it can sort
 12a, 12b, 12c
>
> Any help will be appreciated.
> Jun
>
>
> Marc Ottone <marc.ottone_at_cjb.ville-ge.ch> wrote:
>
> : Hi,
 

> : In order to select data (block based on table) I need to get NUMERIC
 value
> : from VARCHAR
> : like 'X -123b' = 123
> : and use criteria like >100 and <200
> : What would be the simple way to do this?
> : [FORMS 6i / Rdb 7]
> : TIA,
 
> : Marc
 

> : PS:
> : TO_NUMBER generate -01722: Invalid number error
>
>
Received on Tue Feb 20 2001 - 19:32:35 CET