We help IT Professionals succeed at work.

Testing VARCHAR2 for Numeric

mtakasaki
mtakasaki asked
on
Is there a function or way of testing a VARCHAR2 field to see if it contains numeric data (something similar to the IsNum() function in VB. I do not want to use a conversion function, like to_num(), becuase I want to keep the value as a VARCHAR2 and I don't want it to generate an error.
Comment
Watch Question

This would depend on whether or not that VARCHAR2 field may contain only all numeric data or a mixture ('ABC123', for example. If it is a matter of either just characters or just numbers, you can test using the BETWEEN function.  For example, consider this table:

CREATE TABLE test1 (
col1 VARCHAR2(9)
);

You could test for numbers like this:

SELECT *
FROM test1
WHERE LPAD(col1,9,'0') BETWEEN '000000000' AND '999999999';

Just be aware of the width of your column and pad up to that many characters and compare to that many characters in your WHERE clause.

However, this will not work if you have mixed values of characters and numbers.  You will need to pull the values into a cursor and test each character in the string for values between '0' and '9'.

Andrew

Commented:
mtakasaki,
 Something like grep would be great in this siutation!
Unfortunately, I'm not sure if there is a built-in function to do it cleanly so I wrote my own. Basically, the function sets a variable to a value different/same as the string you passed in (depending on whether or not there are numbers in the string), tests for equality, then returns a boolean:

CREATE FUNCTION scanForNum(str VARCHAR2)
RETURN BOOLEAN
IS
 newstr VARCHAR2(30);
 BEGIN
  newstr := TRANSLATE(str,'0123456789','*');
  IF newstr != str
  THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
 END;

You can modify it in a number of ways, for example, turn it into a procedure which prints out a message if a number exists in the string you pass.
 I've always found this implementation of the function to be most helpful in my pl/sql code.
   
                                         Hope this helps,
                                          Antonio
CERTIFIED EXPERT
Top Expert 2005

Commented:
mtakasaki,
  Unfortunately, Andrew's between wouldn't work ('3SLSKD' would be between).  In order to find out if a field is all digits, try:

where translate(col,'0123456789','**********') = rpad('*',length(col),'*') and
      col != rpad('*',length(col),'*')

You can actually use a null translate as well (but I find this harder for people to get the first time):
where translate(col,'0123456789','') is null and
      col is not null;

If you can have negative values, then you need:

where nvl(translate(col,'-0123456789','-'),'-') = '-' and
      nvl(col,'-') != '-';

Good luck!
CERTIFIED EXPERT
Top Expert 2005
Commented:
mtakasaki,
  And Antonio's function, while a good idea, will return true for a string that has ANY digits in it ('ASD4jj' would be TRUE).  To create your own isNum:

create function isNum(col varchar2) return boolean is
begin
  if nvl(translate(col,'-0123456789','-'),'-') = '-' and
     nvl(col,'-') != '-' then
          return(true);
  else return(false);
  end if;
end;

Explore More ContentExplore courses, solutions, and other research materials related to this topic.