Support IN clause when defining variables

[narayanb1]

Suppose we have this query:

select count(*) from T
where day IN ({{day}});

Specifying text variable day as Sunday works. Presumably, that translates to:

where day IN ('Sunday')

However, a typical use case for IN would need a list of items to be specified. Can't specify day as something that would translate to:

where day IN ('Sunday','Monday')

Tried the obvious hack -- specified day as Sunday','Monday -- hoping that if the entire text is enclosed in single quotes, but it gave me no results. I'm guessing the quotes in the input are being removed first.

Can we please add support for an array? Even the hacky version described would suffice.

⬇️ Please click the 👍 reaction instead of leaving a +1 or 👍 comment

[sabino]

@narayanb1 I've managed to make it work like this in a hacky way:

PostgreSql

select count(*) from T
where day = ANY (string_to_array({{day}},  ','));

MySql

select count(*) from T
where find_in_set(day, {{day}}) <> 0;

[narayanb1]

Thanks, @sabino . This hack worked for me, too. I am no longer blocked on this.

[camsaul]

Related to #3567/#5457. We implemented support for doing this with numbers in 0.25.0

[chenlingmin]

But 0.25.0 not support separated str

[camsaul]

That is correct @chenlingmin

[sldorman]

Does anyone have a work around for SQL Server?

[sabino]

@sldorman never worked with SQL Server but this might help http://www.sommarskog.se/arrays-in-sql.html

[aviggiano]

Thanks @sabino (although a native solution would be welcome)

[EshankJain]

Is there a similar solution for Amazon Redshift ?

[PradeepDoddarangaiah]

Does anyone have a work around for SQL Server?

This works for T-SQL

Select Textfield , field2, field3 from Table1 where Textfield in (SELECT value FROM STRING_SPLIT({{input}},','))

[austin-e]

If anyone needs a hacky solution for Snowflake:

select count(*) from T
WHERE CONTAINS( CONCAT({{DAY}}) , DAY) = 1 ;

[uptownnickbrown]

And a hacky solution for Vertica. None of the other hacks worked for me - string_to_array appears to have been added recently, but we're a few version behind so it isn't available for me. It would be much cleaner than this ugliness:

select count(*) from T
where  REGEXP_LIKE(day::varchar, '^' || REPLACE({{day}}, ',', '$|^') || '$');

[flamber]

Redshift used to require a very alternative approach than Postgres, but now it's possible to do something like this:

SELECT * FROM "my_table"
WHERE "my_column" = ANY(STRING_TO_ARRAY(REGEXP_REPLACE(TRIM({{text_input}}), '\\s*,\\s*', ','), ','))

The old alternative approach:

SELECT * FROM "my_table"
WHERE LOWER("my_column") IN (
    -- This splits a comma-separated string into rows. If there might be more than 5 expected splits, then add more `UNION ALL SELECT X`
    SELECT LOWER(TRIM(SPLIT_PART({{text_input}}, ',', "numbers"."n"))) AS "rows" FROM (SELECT 1 AS "n" UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS "numbers" WHERE "rows"<>'' AND "rows" IS NOT NULL
)

Related to #5245

[Niceplace]

For future references, I'm adding a BigQuery compatible snippet. Works with a filter of type "text" This will allow you to pass either a single value as a string or multiple values in the form of a comma-separated string. The value is mapped to param and we are assuming that the filter type is text

I added extra line breaks for clarity.

SELECT some_field1, some_field2, some_field3
FROM some_table
WHERE some_field1 IN  (
  SELECT * FROM UNNEST(
    SPLIT({{param}}, ',')
  )
)

[gabesolomon10]

This array-based solution worked for snowflake:

select count(*) from T where array_contains(day::variant, STRTOK_TO_ARRAY(concat_ws(',', {{day}}), ' ,')) = TRUE

Basically converting the input {{day}} values into an array, and then properly querying that array using array_contains.

[SkymaxPlay]

I figured out how put values into filter from another table. Here is example. Variable type: Field filter Table: machines Column: machine_name (MS)SQL question:

WITH filter_values AS (
SELECT DISTINCT machine_name FROM machines WHERE {{machine_name}}
)

SELECT cp_machine_name, other_column FROM target_table WHERE cp_machine_name IN (SELECT machine_name FROM filter_values)

Works with target table as metabases question or model too.