Using SQL wildcard with SQL Database Connector

Need help Plugins
1

I am trying to do a SELECT with a LIKE condition with the parameter having a wildcard in it so that when a user searches in an input element, they don’t have to search an exact match for the sql server to return something. However when I set up the query in the SQL Database connection plugin, It gives me and error that I can’t figure out.

Here is the query:

query
query854×339 16.4 KB

Here I have tried it several different ways. If I try it with the single quotes I get:
With quotes

If I try it without quotes I get"
Without quotes

I don’t believe its an issue with the external server. If i run the same query directly on the server and substitute the $1 parameter with a letter, I do get results returned.

It appears that the SQL Database Connector doesn’t like the wildcard character “%”. But I am not sure how I could achieve the result I want doing it a different way.

Also if it matters it is a PostgreSQL server running on AWS. I have connected to it and have no issues running a regular SELECT or INSERT query via the database connector plugin.

Thanks!

2

I am basically trying to search an external database from the users input without having to make the user type an exact match. I have tried fixing this with the LIKE condition in the sql command but I get the results above. I have also tried using 2 different autocomplete plugins that allow you to do this. (Basically search for a partial match) These work great when using the built in database of bubble but I run into the same problem when using it with the SQL database. The SELECT query without the LIKE condition is always looking for an exact match.

Wondering if anyone knows how to fix the error message I am getting above or if they know another method I could try?

Thanks!

3

Hey Brandon,

Have a look there :

In your case, maybe LIKE ? would work ?

Michel

4

Hey Michel,

I took a look at that thread and it doesn’t seem to help for my case. when changing the $1 to a ? I still get the same error. Good idea though. The error message comes from me adding in the % wildcard. The sql editor/plugin doesn’t seem to like that character. When I add it it seems to think there is not longer a valid parameter.

If I remove the % it works. Both when I use $1 and ?. But the % is for the query to allow for a search match before/after anything that the use types.

5

Oh I see… Don’t have the answer so, and interested by the solution.

Hope someone here will give other ideas.

Michel

6

Thank you though. I am going to try to do it with some regular expressions instead to see if that will work.

Maybe someone else has and idea though.

7

Brandon,
I have been looking for an answer for this and believe I figured it out. I have two suggestions.

  1. Use concat() to append the wildcard entry
  2. Use rtrim() to trim off any trailing spaces.

Here is the search clause that I used to get it to work.
select sum(inventory) as inventory, sum (market_price) as marketprice where company_shortname = @shortname and ( brand like concat(’%’,rtrim(@cat_name),’%’))

This is not elegant but it works. If I find a cleaner path I’ll post.
Best
Varun

8

Look at what i did.
image