SQL & PL/SQL

CREATE OR REPLACE function IS_NUMBER(str in varchar2) return varchar2 IS
dummy number;
begin
dummy := TO_NUMBER(str);
return ('TRUE');
Exception WHEN OTHERS then
return ('FALSE');
end;

So simple, I didnt think of catching Exception. Can you tell I am a newbie?

Thank you very much!

Hi,
My solution might not be an efficient way to check the number. But you can use it without writing a function.

1 select
2 DECODE(replace(translate('12123','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
3* from dual
SQL>/

DECODE
------
NUMBER

1 row selected.

1 select
2 DECODE(replace(translate('xyz12cv123abc','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
3* from dual
SQL>/

DECODE(RE
---------
NON NUMER

1 row selected.



Regards

SQL>create or replace function is_num(n varchar2)
return varchar2 is
begin
for v in 1..length(n) loop
if not((ascii(substr(n,v,1))>=48) and (ascii(substr(n,v,1))<=57)) then
return 'F';
end if;
end loop;
return 'T';
end;
/
SQL> select is_num('25') from dual; -----It returns T
SQL> select is_num('2A4R') from dual; -- it returns F

Try it --- Jameel

SQL>&nbsp;select
&nbsp;&nbsp;2&nbsp;&nbsp;DECODE(replace(translate('-12123','1234567890','##########'),'#'),NULL,'NUMBER','NON&nbsp;NUMER')
&nbsp;&nbsp;3&nbsp;&nbsp;from&nbsp;dual;

DECODE(RE
---------
NON&nbsp;NUMER

SQL>&nbsp;select
&nbsp;&nbsp;2&nbsp;&nbsp;DECODE(replace(translate('12,123','1234567890','##########'),'#'),NULL,'NUMBER','NON&nbsp;NUMER')
&nbsp;&nbsp;3&nbsp;&nbsp;from&nbsp;dual;

DECODE(RE
---------
NON&nbsp;NUMER

SQL>&nbsp;

http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

this will give u a better understanding
cheers
SHUBH

Hi,
Sorry for not considerinf . , and - .

You can include any character as part of number


SQL>select
2 DECODE(replace(translate('-.,12123','-.,1234567890','#############'),'#'),NULL,'NUMBER','NON NUMER')
3 from dual;

DECODE
------
NUMBER

1 row selected.

SQL>select
2 DECODE(replace(translate('-.,XX12123','-.,1234567890','#############'),'#'),NULL,'NUMBER','NON NUMER')
3 from dual;

DECODE(RE
---------
NON NUMER

1 row selected.

Regards

Are you saying that this -.,12123 is numeric?
I don't think so...

SQL> create table t
  2  (x int);

Table created.

SQL> insert into t values (-.,12123);
insert into t values (-.,12123)
                       *
ERROR at line 1:
ORA-00936: missing expression

Not to mention the fact that IP addresses aren't numeric, despite consisting wholly of digits and points (255.255.255.0).

This comes up from time to time and the easiest way of determining whether some string is numeric is to write a function to trap the ORA-1722 (or whatever) exception. If performance is a real issue we can always use native compilation. Although by now Oracle really ought to provide these things as built-ins. I guess it's that old problem about SQL not supporting BOOLEAN datatypes again.

Cheers, APC

I know its kind of late to reply here... but here's another way to do it (10g though):

select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
-- Returns NUMBER

select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
-- Returns NOT_NUMBER

So, 12.34 isn't a number?

SQL> select decode(REGEXP_INSTR ('12.34', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') from dual;

DECODE(REG
----------
NOT_NUMBER

I think there is a small bug in the regular expression example in general. Not only is 12.34 not a number, but neither is 1234.

SQL>select decode(REGEXP_INSTR ('1234', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') f
rom dual;

DECODE(REG
----------
NOT_NUMBER

To use regular expressions in this way we need an extra bracket to search for any "non digits".

SQL>select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
DECODE
------
NUMBER

SQL>select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'
) from dual;

DECODE(REG
----------
NOT_NUMBER

However, we still have the issue that SomeOne else brings up. This approach will only work on positive integers.

I have to say that Avinash's approach with translate, replace, and decode was a fun one to pick through.

The problem is not in that members regular expression but in the way the forum treats square brackets if you don't include {noformat}

{noformat} tags around the code.

The square brackets are in the code, you just can't see them because of the forum if the code hasn't been formatted.  (just as it's done with your code)  ;)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

What Kaushik_Orcl had actually posted was...

select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
-- Returns NUMBER

select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
-- Returns NOT_NUMBER

Which you would see if you reply to his post and quote his text.