How does one convert an array of string values into an array of numbers?
Background:
Using Snowflake, I have a string input like "123, 45, 89" that I want to convert to an array of numbers (123, 45, 89) so that I can eventually compare against an int column like
WHERE id IN ( array of number here)
I think I can use SPLIT('123, 45, 89', ',') to get an array of strings, but get an error like this:
SQL compilation error: Can not convert parameter 'SPLIT(?, ',')' of type [ARRAY] into expected type [NUMBER(38,0)]
For context, I am using Metabase, with a text filter as input to add those ids.
You could try to leverage the ARRAY_CONTAINS function, instead of the WHERE IN option. The tricky part is that the SPLIT function creates an array of strings, not numbers, so you'd have to convert the id field into a string, first. You also need to be careful of spaces in your initial string, as that can create issues, as well. However, something like this works:
CREATE OR REPLACE TEMP TABLE testing (id int);
INSERT INTO testing (id) VALUES (45);
Then when querying this table with your string/array:
SELECT t.id
FROM testing t
WHERE array_contains(t.id::varchar::variant,SPLIT('123,45,89',','));