Convert an array of strings to an array of numbers in Snowflake

Question

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.

Answer 1

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',','));

Answer 2

Using STRTOK_SPLIT_TO_TABLE:

Tokenizes a string with the given set of delimiters and flattens the results into rows.

SELECT * 
FROM t
WHERE id IN (SELECT s.value::INT 
             FROM TABLE(STRTOK_SPLIT_TO_TABLE('123,45,89',',')) s
             );

Or using the same pattern but as a JOIN:

SELECT t.*
FROM t
JOIN TABLE(STRTOK_SPLIT_TO_TABLE('123,45,89',',')) s ON t.id = s.value::INT;

---

How does it work:

Data prep:

CREATE OR REPLACE TABLE t(id INT, col TEXT);
INSERT INTO t(id, col) VALUES (1, 'a'), (2,'b'), (45, 'c');

Subquery(at this point input could be treated as ordinary table):

SELECT s.value::INT FROM TABLE(STRTOK_SPLIT_TO_TABLE('123,45,89',',')) s;
-- 123
-- 45
-- 89