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: [EMAIL PROTECTED]
Glenn Travis
<c-glenn.travis To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
@wcom.com> cc:
Sent by: Fax to:
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
