Re: Simple problem...

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Fri, 28 Feb 2003 01:03:20 GMT
Message-ID: <Xns932FCB6EADF1Cpobox002bebubcom@204.127.199.17>

"Telemachus" <tollg_at_tendwa.rns.net> wrote in news:Asr7a.12231$V6.16492_at_news.indigo.ie:

> Thanks Andrew...
> 
> I just wondered if there was another way than  6502
> "Andrew Allen" <andrew.allen_at_handleman.com> wrote in message
> news:3E5E3A82.8020605_at_handleman.com...

>> Telemachus wrote:
>> > But I can't find an easy solution.
>> >
>> > For 817 without rolling your own PL/SQL func...
>> > Is there a fast builtin way to do IS_A_NUMBER('STRING') or
>> > is_a_float()

> or


>> > is_an_integer()

>> >

>> >

>> > i.e. given a string return true if the string represents a valid
>> > number

> or

>> > false. Or do most people trap an error from TO_NUMBER ?
>> >
>> how about something like this
>> SQL$ declare num_var number;
>> 2 begin
>> 3 select 'abc' into num_var from dual;
>> 4 end;
>> 5 /
>> declare num_var number;
>> *
>> ERROR at line 1:
>> ORA-06502: PL/SQL: numeric or value error: character to number
>> conversion error
>> ORA-06512: at line 3

>>

>> so, if you get an ORA-6502 then the number is not numeric.
>> Handle this in the exception part of your function and you are all
>> set. I leave the details up to you, but it is simple enough.
>> --
>> Andrew

>>

Hello,

You can do it without exceptions if you use translate to remove all the numeric characters and check for null. See this example below. Note the 'x' is just a dummy translation to itself so that transalate won't always return null.

SQL> select * from t;

STRING

12345
a12345
12b345
12b345xx
987
987.01
1,987.01
987*
999
953

10 rows selected.

SQL> select to_number(string)
2 from t where
3 translate(string,'x0123456789','x') is null;

TO_NUMBER(STRING)

SQL> select to_number(string)
2 from t where
3 translate(string,'x.0123456789','x') is null;

TO_NUMBER(STRING)

Also see this link for a longer example, URL may wrap.

http://groups.google.com/groups?&selm=Xns927AD545426D9pobox002bebubcom% 40216.148.227.77

Hth
Martin Received on Thu Feb 27 2003 - 19:03:20 CST