How can I know which values are numeric in oracle 9i

I have this database which contains a varchar.

I want to know which records holds numeric values. I tried REGEXP_COUNT and other but I'm running on 9i and I think this is for 10g >

How can I achieve this?

I tried:

 select to_number( my_column ) from my_table 

But it doesn't work, because well not all of them are numeric.

EDIT

Background.

This table contains employee id's, all of which are numeric ( read 1234 or 24523 or 6655 )

The in the initial database load, when the employee id was unknown instead of using something like -1 they entered texts like:

NA, N/A, NONE, UNK, UNKNOW, TEST, EXTERNAL, WITHOUT_ID

Really the main 开发者_开发百科fault is, that column is varchar and not number as it should.

Now, what I try to do, is to get ll the records that are not numeric ( that don't contain an employee id ) but since that db is 9i, I could not use RegExp

I am afraid you'll have to write your own isnumber function, and then use it, something like this (untested) found in this thread, should work.

 DECLARE FUNCTION isNumber(p_text IN VARCHAR2) RETURN NUMBER IS
 v_dummy NUMBER;
 not_number EXCEPTION;
 PRAGMA EXCEPTION_INIT(-, not_number);
 BEGIN
     v_dummy := TO_NUMBER(p_text);
     RETURN 1;
   EXCEPTION
   WHEN not_number THEN RETURN 0;
 END is_number;

After that you could use a decode function combined with your isnumber function to get the results you need.

Just another pure SQL workaround:

select my_column
  from my_table
 where translate(my_column,'x0123456789','x') is null;

Depends on what you count as 'numeric'. Do you allow negative numbers, decimals or just integers, or scientific notation (eg '1e3'). Are leading zeroes allowed ?

If you just want positive integer values, try

where translate(col,' 1234567890','0') is null

Try this

CREATE OR REPLACE PACKAGE value_tests
AS
   FUNCTION get_number( pv_value IN VARCHAR2 ) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY value_tests
AS
   FUNCTION get_number( pv_value IN VARCHAR2 ) RETURN NUMBER
   IS
      converted_number NUMBER;

      invalid_number EXCEPTION;       
      PRAGMA EXCEPTION_INIT( invalid_number, -01722 );

      value_error EXCEPTION;       
      PRAGMA EXCEPTION_INIT( value_error, -06502 );

   BEGIN
      <<try_conversion>>
      BEGIN
         converted_number := TO_NUMBER( pv_value );
      EXCEPTION
         WHEN invalid_number OR value_error
         THEN 
            converted_number := NULL;
      END try_conversion;

      RETURN converted_number;
   END get_number;
END;
/

Running it on this...

select my_column
     , value_tests.get_number( my_column ) my_column_num
  from (           select 'mydoghas3legs' my_column from dual 
         union all select '27.5' my_column from dual
         union all select '27.50.5' my_column from dual
       )

returns

MY_COLUMN     MY_COLUMN_NUM
------------- -------------
mydoghas3legs
27.5                   27.5
27.50.5

I do not like using exceptions in normal code but this seems to be the best and safest aproach:

CREATE OR REPLACE FUNCTION "IS_NUMBER" (pX in varchar2) return integer is
       n number;
begin
     n:=to_number(pX);
     return 1;
     exception
              when others then
                   return 0;
end;

I manage to work around like this:

select my_column
from my_table
where my_column not like '%1%'
and my_column not like '%2%'
and my_column not like '%3%'
and my_column not like '%4%'
and my_column not like '%5%'
and my_column not like '%6%'
and my_column not like '%7%'
and my_column not like '%8%'
and my_column not like '%9%'
and my_column not like '%0%' 

Dirty, but it works. ;)

Yet another approach, here's a function I wrote some time ago:

CREATE OR REPLACE function string_is_numeric
 (p_string_in in varchar2)
return boolean is
begin
  for i in 1..length(p_string_in) loop
    if substr(p_string_in, i, 1) not in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') then
      return false;
    end if;
  end loop;
  return true;
end;
/

As pointed out in pedromarce's answer, you might need to change that from a boolean return to number or varchar2 to better suit your needs.

Here's my version of the routine, similar to that posted by pedromarce. Note that the posted example doesn't compile due to the "-" exception number. This example compiles and works:

create or replace function IsNumber(
  a_Text varchar2
) return char is
  t_Test               number;
begin
  begin
    t_Test := to_number(a_Text);
    return 'Y';
  exception when value_error then
    return 'N';
  end;
end;

Example usage:

select IsNumber('zzz') from dual;

Result: N

select IsNumber('123.45') from dual;

Result: Y