[SOLVED] Snowflake SQL. Select column value if 1st 5 characters are numeric and within a Numeric Range

Snowflake SQL. Select column value if 1st 5 characters are numeric and within a Numeric Range

I have a column named ProcedureCode VARCHAR(16777216) in a Table named ProcedureCode. It has values like '12345,46H', 'A4715,12W', '98765', '99200' etc.

From that Table, I need to pull only rows where the 1st 5 characters are Numeric and the 1st 5 characters fall within a range 99200 - 99499.

In T-SQL, I would use IsNumeric() to determine if the 1st 5 characters of the string are numeric [but I can't use IsNumeric () in Snowflake]. Then I would Cast the 1st 5 characters as Numeric. Then I would test if the 1st 5 are in my range.

I don't know how to do this in Snowflake.

I would appreciate any pointers/help I can get.

Thanks!

Solution

You can use try_to_number on the left 5 characters. If it's not numeric, it will return null. You can then compare that to your range:

create or replace table T1(S string);
insert into T1(S) values('99200ABC'), ('ABC12345'), ('12345ABC');

select * from T1 
where try_to_number(left(S, 5)) between 99200 and 99499;

This will return only the row that is numeric for the left five characters and is in range. It filters out the 12345ABC because its digits are out of range, and filters out ABC12345 because the try_to_number function returns NULL for it.