RE: Convert character string to number

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 25 Jan 2001 08:53:42 -0800
Message-Id: <10752.127504@fatcity.com>

Also the subtrng method rejects numbers written in scientific notation.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
From: jkstill_at_cybcon.com [mailto:jkstill_at_cybcon.com] Sent: Thursday, January 25, 2001 8:02 AM To: Multiple recipients of list ORACLE-L Subject: Re: Convert character string to number

On Wed, 24 Jan 2001, Helmut Daiminger wrote:

> I want to read a column (varchar2) from a table and convert the contents
> into numbers if the string consists of numbers only. If the string contains
> characters, I don't want to convert it.
>
> Example for data in varchar2 colum:
>
> row 1: 12345
> row 2: text
> row 3: 123dfe
> row 4: 9876432
>
> I can easily conver row 1 and 4 using the to_number function, right? But how
> can I tell Oracle to skip rows 2 and 3 since the character field also
> contains characters not just numbers?
>
> Any idea?

One of my favorite questions. Why?

Because there will inevitably be posts using substr() or something similar, and I get to shoot 'em down. ;)

Use of string functions will really slow down your code. If you rely on internal oracle error trapping to do this, it will be pretty fast.

Code below.

Jared

drop table num_test;

create table num_test (

value varchar2(20) not null
)
/

insert into num_test values('12345');
insert into num_test values('text');
insert into num_test values('123dfe');
insert into num_test values('9876432');

create or replace function is_number( chk_data_in varchar2 ) return integer
is

dummy number(38,4);
begin

	dummy := to_number(chk_data_in);
	return 1;

exception
when value_error then

return 0;
when others then

raise;
end;
/

show errors function is_number

the +10 in the second column serves to prove that
conversion is only taking place on numeric values select decode(is_number(value), 0, value, 1, to_number(value)), decode(is_number(value), 0, value, 1, to_number(value) + 10) from num_test /

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

Received on Thu Jan 25 2001 - 10:53:42 CST