How to find out which values ​​are numeric in oracle 9i

Question

I have this database that contains varchar.

I want to know which entries contain numerical values. I tried REGEXP_COUNTothers, but I work 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 this does not work, because not all of them are numerical.

EDIT

Background.

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

In the database startup, when the employee identifier was unknown, instead of using something like -1, they entered texts such as:

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

Indeed, the main mistake is that the column varchar, not the number, is as it should.

, , , ( ), db 9i, RegExp

Answer 1

:

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%' 

, .;)

Answer 2

, isnumber, , - (), thread, .

 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;

, .

Answer 3

SQL:

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

Answer 4

, "". , (, "1e3" ). ?

,

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

Answer 5

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;
/

...

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
       )

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

Answer 6

, 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;

Answer 7

, , :

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;
/

pedromarce, varchar2, .

Answer 8

Here is my version of the subroutine, similar to the one posted by pedromarce. Note that the published example does not 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;

Usage example:

select IsNumber('zzz') from dual;

Result: N

select IsNumber('123.45') from dual;

Result: Y