Comparação de intervalo de valores numéricos de string SQL

Como Ken White mencionou em seu comentário acima, o design da sua tabela não é o ideal. O ideal é que você armazene cada componente numérico em uma coluna numérica de algum tipo. Nesse caso, a comparação seria bastante trivial. No entanto, poderíamos contornar o problema da seguinte forma:

SELECT *
FROM yourTable
WHERE CAST(REGEXP_REPLACE(str, '^[A-Z]+', '') AS NUMBER(12,2)) BETWEEN 17.0 AND 19.0;

Aqui, estamos removendo as letras iniciais da coluna de string, convertendo para numérico e, finalmente, fazendo uma BETWEENcomparação de intervalo.

Parece que os valores comparados são uma espécie de "versões", o que requer "classificação natural". Isso pode ser feito com SQL:

SELECT value, lower_limit, upper_limit,
  TRANSFORM(REGEXP_EXTRACT_ALL(value,'(\\d+|[^\\d]+)'),e->IFF(e RLIKE '\\d+',e::INT,e)) 
  BETWEEN TRANSFORM(REGEXP_EXTRACT_ALL(lower_limit,'(\\d+|[^\\d]+)'),e->IFF(e RLIKE '\\d+',e::INT,e))
      AND TRANSFORM(REGEXP_EXTRACT_ALL(upper_limit,'(\\d+|[^\\d]+)'),e->IFF(e RLIKE '\\d+',e::INT,e)) 
  AS is_value_between_lower_upper_limit
FROM VALUES ('A18.30','A17.0','A19.0'),
            ('P1A.5', 'P1.0', 'A2.0') AS s(value, lower_limit, upper_limit);

Saída:

Mais em: Como classificar strings de "versão" com SQL no Snowflake?

Alternativamente usando UDF:

create or replace function natural_sort(str TEXT)
returns array
language python
runtime_version = '3.11'
packages = ('natsort')
handler = 'sort'
as
$$
import natsort
def sort(str):
  return list(natsort.natsort_key(str))
$$;

SELECT value, lower_limit, upper_limit,
 natural_sort(value) BETWEEN natural_sort(lower_limit) 
                     AND natural_sort(upper_limit) AS is_value_between_lower_upper_limit
FROM VALUES ('A18.30','A17.0','A19.0'),
            ('P1A.5', 'P1.0', 'A2.0') AS s(value, lower_limit, upper_limit);

Saída: