PostgreSQL Query Text String Data from TextBox Input

Queries and Resources
, ,
1

I'm trying to create a table component search box on a text value in the datasource.

I created a table component on the retool page that updates data from a query that takes input from a textbox:

SELECT * FROM "database"."test_table3" where ticker in ({{textInput1.value}})

If I enter into textbox values 'AAPL','MSFT'
It will send:
SELECT * FROM "database"."test_table3" where ticker in ('AAPL','MSFT')

However, if I enter textbox values AAPL,MSFT
It will send:
SELECT * FROM "database"."test_table3" where ticker in (AAPL,MSFT)

And that query will not work since the data is text.

How can I change my query so the textbox value can be AAPL,MSFT
and it will send the equivalent query:
SELECT * FROM "database"."test_table3" where ticker in ('AAPL','MSFT')

I did try this, but did not work:
SELECT id,CAST(ticker as TEXT),price_date,close,vol FROM "database"."test_table3" where ticker in (AAPL,MSFT)

2

Hello!

Thank you for writing into the community forums! First, I wanted to just share this great walkthrough we have on adding a search bar to a table:

https://docs.retool.com/docs/working-with-tables#adding-a-search-bar

So, in your backend, you have all of your values saved as 'AAPL', 'MSFT', 'TSLA', etc. and the query isn't returning anything without exact values? Is it more of a type mismatch or is it the actual quotes that are just not matching?

If it's the quotes, can you try the wild card ('%') similar to this?

1178×356 25.9 KB

The other option is to potentially write some Javascript that adds quotation marks to each input, but let's see if this works first!

3

The backend table has a text column called TICKER with each row containing a different text values such as: AAPL, MSFT, TSLA. Each item in this list is representing a separate row. The search text box needs to search the backend table such that a search textbox entry AAPL, MSFT, TSLA is translated to the query that looks like: SELECT TABLE WHERE ticker IN ('AAPL','MSFT','TSLA').

I tried the '%' with the following results, but none succeeds to allow my search with multiple values:
Does NOT work: where ticker ILIKE {{'%' + AAPL, MSFT, TSLA + '%'}}
Does NOT work: where ticker ILIKE {{'%' + AAPL + '%'}}
WORKS: where ticker ILIKE {{'%' + 'AAPL' + '%'}}

How can I make a search box simultaneously search with multiple text values (TextValue1, TextValue2, TextValue3) in a SQL data source?

1 Like
4

Would using a multiSelect work for you instead? The IN operator will take an array of comma-separated values, which you can do in the text Input, but would take some additional JS. Here is the value in the multiSelect:

1948×366 50 KB

Versus the textInput:

1932×254 31.6 KB

Where you would have to use .split() on either a space or a comma depending on the user input.

So something like, textInput2.value.split(" ") or textInput2.value.split(", ")

Please, let us know if this does or doesn't work out! Could you also let us know what flavor of SQL you're using? (Postgres, MySQL, etc)