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

From: FOX, Simon <Simon.FOX_at_crewe.sema.slb.com>
Date: Mon, 07 Oct 2002 03:23:41 -0800
Message-ID: <F001.004E1FBD.20021007032341@fatcity.com>

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

0161 601 8723

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

Hi,

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

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

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

TIA

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 fatcity.com 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 Postmaster_at_fatcity.com for clarification.

------_=_NextPart_001_01C26DEB.9E65F7A0--
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: FOX, Simon
INET: Simon.FOX_at_crewe.sema.slb.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Mon Oct 07 2002 - 06:23:41 CDT