Re: Check for number only

You could always write your own function. Or you could do something using the replace() function, maybe like this (again, untested):

select circ_id, circ_num from circuit
where replace(circ_num,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',

     'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ') is not null;

Depending on your data, you may need to check for punctuation and special characters as well.

HTH, Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: Diana_Duncan_at_ttpartners.com

                                                                                       
                              
                    Glenn Travis                                                       
                              
                    <c-glenn.travis        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>       
                    @wcom.com>             cc:                                         
                              
                    Sent by:               Fax to:                                     
                              
                    root_at_fatcity.co        Subject:     Check for number only          
                              
                    m                                                                  
                              
                                                                                       
                              
                                                                                       
                              
                    05/18/2001                                                         
                              
                    12:17 PM                                                           
                              
                    Please respond                                                     
                              
                    to ORACLE-L                                                        
                              
                                                                                       
                              
                                                                                       
                              

I have a table with a column of varchar2(50) datatype. We are building a new table and the values in this column should all be numbers. I need to query the table to find all rows which have a value in this column which is not a valid number.

Table:
circuit (circ_id (primary_key) number, circ_num varchar2(50))

Example: find all values in circ_num which are not numbers. how do I do something like 'select circ_id, circ_num from circuit where circ_num not valid to_number(circ_num)'

Is there a function to check this?

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
  INET: c-glenn.travis_at_wcom.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).




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