Home » SQL & PL/SQL » SQL & PL/SQL » Numeric check
Numeric check [message #163279] Wed, 15 March 2006 22:46 Go to next message
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?
Re: Numeric check [message #163285 is a reply to message #163279] Wed, 15 March 2006 23:34 Go to previous messageGo to next message
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
Re: Numeric check [message #163297 is a reply to message #163285] Thu, 16 March 2006 00:34 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select nvl2(translate('&n', 'A1234567890','A'), 'F', 'T')
from dual
/

_____________
Ross Leishman
Re: Numeric check [message #163318 is a reply to message #163297] Thu, 16 March 2006 03:13 Go to previous messageGo to next message
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
Re: Numeric check [message #163476 is a reply to message #163318] Thu, 16 March 2006 20:06 Go to previous messageGo to next message
srajan72
Messages: 20
Registered: February 2006
Junior Member
Thanks guys! I really appreciate your response.
Re: Numeric check [message #384653 is a reply to message #163279] Wed, 04 February 2009 11:46 Go to previous message
KMCK111
Messages: 1
Registered: February 2009
Junior Member
Thanks rleishman, very succinct.
Previous Topic: Cut data into groups
Next Topic: Wrap tool
Goto Forum:
  


Current Time: Thu Mar 28 12:01:43 CDT 2024