Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to retrieve numeric values only from a varchar2?

RE: how to retrieve numeric values only from a varchar2?

From: FOX, Simon <>
Date: Mon, 07 Oct 2002 03:23:41 -0800
Message-ID: <>

Could you use to_number?  

It will work for numeric data and fail for non-numeric.  

See Feuerstein PL/SQL Programming p 882.  

Simon Fox

Room 905, CRH

-----Original Message-----
Sent: 07 October 2002 10:13
To: Multiple recipients of list ORACLE-L


Can anyone point me in the right direction. In my table I have a varchar2 column that contains a label that could be either text or numeric data. I need to update another column in the same table based only on the rows in the first column that are numeric. The values are in the range 001 to 999 only.  

I have tried the following piece of pl/sql, unsuccessfully  

for i in 1..999
update tdcr set features=db_connect.e_features(132) where label = to_char(i,'099');
end loop;

Would anyone be able to tell me where I am going wrong or suggest an efficient piece of sql to perform the task.  


This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact for clarification.


Please see the official ORACLE-L FAQ:

Author: FOX, Simon

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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). Received on Mon Oct 07 2002 - 06:23:41 CDT

Original text of this message