Re: Convert character string to number

From: <jkstill_at_cybcon.com>
Date: Thu, 25 Jan 2001 08:01:52 -0800 (PST)
Message-Id: <10752.127496@fatcity.com>

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)

Received on Thu Jan 25 2001 - 10:01:52 CST