OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Numeric check

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Numeric check [message #163279]

Wed, 15 March 2006 22:46

srajan72
Messages: 20
Registered: February 2006

Junior Member

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. Seems very inefficient.

I am looking for a function that would do the following:

IS_Number('123a34') = 'F'
IS_Number('456') = 'T'

Is there a better way to achieve this?

Report message to a moderator

Re: Numeric check [message #163285 is a reply to message #163279]

Wed, 15 March 2006 23:34

orajamzs
Messages: 110
Registered: February 2006
Location: hyderabad

Senior Member

SQL>create or replace function is_number(a varchar2)
    return varchar2 is
    begin
    for i in 1..length(a) loop
    if not((ascii(substr(a,i,1))>=48)  and  
         (ascii(substr(a,i,1))<=57))  then
    return 'F';
    end if;
    end loop;
    return 'T';
    end;
    /
Function Created.
SQL>select is_number('35SD34') from dual;

IS_NUMBER('35SD34')
---------------------------------
F

SQL>  select is_number('343') from dual;

IS_NUMBER('343')
---------------------------
T

--- I hope you this function will work
Cheers

Report message to a moderator

Re: Numeric check [message #163297 is a reply to message #163285]

Thu, 16 March 2006 00:34

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

select nvl2(translate('&n', 'A1234567890','A'), 'F', 'T')
from dual
/

_____________
Ross Leishman

Report message to a moderator

Re: Numeric check [message #163318 is a reply to message #163297]

Thu, 16 March 2006 03:13

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

A little faster, rather than looping through each value is to assign the value to a number datatype, if there is an exception then then it is not a number, no exception the it is a number:

  1  create or replace function IsNumber(val1 varchar2)
  2  return varchar2 is
  3  n number;
  4  begin
  5  n:=val1;
  6  return 'T';
  7  exception
  8  when others then
  9  return 'F';
 10* end;
SQL> /

Function created.

SQL> var a varchar2
SQL> exec :a:=isnumber('1234');

PL/SQL procedure successfully completed.

SQL> print a

A
--------------------------------
T

SQL> exec :a:=isnumber('12b34');

PL/SQL procedure successfully completed.

SQL> print a

A
--------------------------------
F

HTH
Jim

Report message to a moderator