Passing Multiple Values to Text Input

Hi Retool team,

I want to filter a table with multiple values passed in a text input. In the plain query language, the syntax looks like this: SELECT * FROM users WHERE id IN (123, 345, 678).
I tried the method mentioned in this post
SELECT * FROM users WHERE id = ANY({{textinput1.value.split(", ")}})
But it didn't work and returned an error message: "SQL compilation error:
Invalid query block: :.".

Could you provide any help on this? Thank you.

If you are using MS SQL Server then this is the pattern to follow:

id in (SELECT value FROM STRING_SPLIT({{textinput1.value}}, ','))

Retool passes a string "123, 345, 678" and you let SQL server do the splitting on its end.

For Postgres I believe this is the right syntax (not tested):

id in (SELECT regexp_split_to_array({{textinput1.value}}, ','))

HI @bradlymathews,

Thank you for the help! I am using Snowflake. And the following syntax works: SELECT table1.value FROM TABLE (SPLIT_TO_TABLE ({{textinut.value}}, ', ')) AS table1.

I am wondering if there any other more efficient solutions besides using a subquery to split the string into an array.

@doris.l ,

Nice and thanks for posting back the solution for others to see.

SPLIT_TO_TABLE looks to be a pretty efficient way to do things. I generally like making the DB server do as much work as possible as it will usually be more efficient than making the client do it.