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.