# IS_NUMBER function?

I wrote a very primitive function to check if the contents of a VARCHAR field is Numeric. The function simply checks if every character in the field is between 0 - 9.

I feel like this is a very inefficient way to check for numeric/non-numeric data.

Is there a better way to do it?
• ###### 1. Re: IS_NUMBER function?
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;
• ###### 2. Re: IS_NUMBER function?
So simple, I didnt think of catching Exception. Can you tell I am a newbie?

Thank you very much!
• ###### 3. Re: IS_NUMBER function?
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
• ###### 4. Re: IS_NUMBER function?
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
• ###### 5. Re: IS_NUMBER function?
SQL> select
2  DECODE(replace(translate('-12123','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
3  from dual;

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

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

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

SQL>
• ###### 6. Re: IS_NUMBER function?

this will give u a better understanding
cheers
SHUBH
• ###### 7. Re: IS_NUMBER function?
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
• ###### 8. Re: IS_NUMBER function?
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```
• ###### 9. Re: IS_NUMBER function?
Are you saying that this -.,12123 is numeric?
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
• ###### 10. Re: IS_NUMBER function?
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
• ###### 11. Re: IS_NUMBER function?
select decode(REGEXP_INSTR ('1234', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') from dual;
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``````
• ###### 12. Re: IS_NUMBER function?
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.
• ###### 13. Re: IS_NUMBER function?
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)  ;)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ``````
• ###### 14. Re: IS_NUMBER function?
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.