Argh. Storing numbers in a varchar2 is a dreadful, terrible, horrible idea!

This highlights one of the issues:

You can store things that aren't numbers in them!

So what you need to do is create a function to ensure the string is in fact a number:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15321803936685

With this you can first check the values are numbers. Then check if they're negative:

create table t (
  n varchar2(100)
);

insert into t values ('1');
insert into t values ('-1');
insert into t values ('a');

select * from t
where  sign(n) = 1;

create or replace function isnumber (val varchar2)
  return number as 
  retval number;
begin
  retval := to_number(val);
  return 1;
exception
  when others then
    return 0;
end;
/

with nums as (
  select /*+ materialize */* from t where isnumber(n) = 1
)
  select * from nums nm where sign(n) = 1;

Or you could go further: create a virtual column that returns the result of the function above. Then you can query that column instead:

create or replace function isnumber (val varchar2)
  return number deterministic as 
  retval number;
begin
  retval := to_number(val);
  return retval;
exception
  when others then
    return null;
end;
/

alter table t add (nn number as (isnumber(n)));

select * from t
where  sign(nn) = 1;

N  NN  
1  1  

Also - sign can't return 2! In fact, why are you using this at all? Why not:

select * from t
where  nn > 0;

N  NN  
1  1