drop table test_204;

create table test_204 (col_1 number, col_2 varchar2(10));

insert into test_204 select lv1
                           ,case when lv1 in (999,1) then 'A'||to_char(lv2) else to_char(lv2) end
                     from   (select level lv1 from dual connect by level <= 1000) x1
                           ,(select level lv2 from dual connect by level <= 100) x2;

create index test_204_idx on test_204(col_1,col_2);

drop index test_204_idx;

select * from test_204 order by col_1;
                           
select col_1, col_2 ,col_1
from   test_204
where  1=1
and    to_number(col_2) >= 5
and    to_number(col_2) <=7
and    col_1+0 = 1000
;

SCOTT@orcl_11g> create table inv_group_ranges
  2    (group_id   number,
  3  	min_value  varchar2 (10),
  4  	max_value  varchar2 (10))
  5  /

Table created.

SCOTT@orcl_11g> insert all
  2  into inv_group_ranges values (483, 'A', 'B')
  3  into inv_group_ranges values (483, 1, 2)
  4  select * from dual
  5  /

2 rows created.

SCOTT@orcl_11g> select *
  2  from   inv_group_ranges
  3  where  group_id = 483
  4  and    to_number (min_value) <= 1
  5  and    to_number (max_value) >= 1
  6  /
and    to_number (min_value) <= 1
       *
ERROR at line 4:
ORA-01722: invalid number

SCOTT@orcl_11g> create or replace function to_number_or_null
  2    (p_string in varchar2)
  3    return number
  4  as
  5  begin
  6    return to_number (p_string);
  7  exception
  8    when value_error then
  9  	 return to_number (null);
 10  end to_number_or_null;
 11  /

Function created.

SCOTT@orcl_11g> select *
  2  from   inv_group_ranges
  3  where  group_id = 483
  4  and    to_number_or_null (min_value) <= 1
  5  and    to_number_or_null (max_value) >= 1
  6  /

  GROUP_ID MIN_VALUE  MAX_VALUE
---------- ---------- ----------
       483 1          2

SCOTT@orcl_11g>

SELECT /*+ NO_MERGE(x) NO_PUSH_PRED(x)*/
       gr.subgroup_id,gr.property_id
FROM   (
    SELECT gr.subgroup_id,gr.property_id
    ,      to_number(gr.min_value) AS min_value
    ,      to_number(gr.max_value) AS max_value
    from xxau.inv_group_ranges gr
    where gr.group_id=483
) x
WHERE 1 BETWEEN gr.min_value AND gr.max_value