Re: number only

select replace(translate(col,A,B),'x',null)
from table

where 

A is string of all ascii chars except '0' to '9'
B is string the same length as A consisting entirely of 'x'

hth
connor

--- Oracle <oracle_list@xxxxxxxxxxx> wrote: > I have a column with data such as 
:
> 
> abc123
> 123
> abcde
> 
> I want to write a query which returns only numbers from the column , if
> there are none, then default to 999
> So i would have:
> 
> 123
> 123
> 999
> 
> Any ideas on the syntax?
> 


=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, 
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------


        
        
                
___________________________________________________________ALL-NEW Yahoo! 
Messenger - sooooo many all-new ways to express yourself 
http://uk.messenger.yahoo.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: