Re: VARCHAR to NUMBER

Smart suggestion! Works like a charm!

Thanks,
Jun
Frank <franjoe_at_frisurf.no> wrote:
: 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...

: (r.page,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
: '0123456789')) asc

:> : 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
:>
:>

-- 
    .&______~*_at_*~______&.     	    Jun Zhang          
  "w/%%%%%%%%%%%%%%%%%%%\w"         Tel: 617 353 2431
    `Y""Y""Y"""""Y""Y""Y'           Fax: 617 353 6340
 /-\_|__|__|_____|__|__|_/-\        Email: junzhang_at_bu.edu
[BOSTON_UNIVERSITY__BIO_DEPT]       http://bio.bu.edu/~junzhang 
__________________________________________________________________________
Get paid when you are $urfing? Yes! 
http://viewbar.isFun.net
___________________________________________________________________________