snowflake cloud data platform – SQL string numeric value range comparison

It looks like the values that are compared are sort of “versions” which requires “natural sort”. It can be achieved with 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);

Output:

More at: How to sort “version” strings with SQL in Snowflake?

Alternatively by using 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);

Output: