Need help with Searching using * or % as wildcards

App Building
, ,
1

User need to be able to search for data using Wildcard or similar

I have a text field
image
and a search button that triggers my query "Get_users"
Note: button is disabled when {{textInput1.values.length<3}}

query Get_users:

SELECT *
FROM db1.table_user  USR 
WHERE USR.fullname LIKE '%'{{textInput1.values}}'%'
LIMIT 100

Results is showing in a table
Search for "Thore" give 1 result as expected
Search for "Th%re" gives 0 result, but 1 result was expected
Search for "t%e" gives 0 result, but 1 result was expected

Need help with allowing wildcards within the value. so users can search for all possible results.

Filter is not supporting % or space

image
image943Ă—127 4.05 KB

Expected 2 results
image
image979Ă—182 5.78 KB

Or if anyone has a idee for me to solve this. please share :slight_smile:

-Thore

1 Like
2

I haven’t tested it but I asked ChatGPT to come up with something. When I’m at my desk I am going to try the following:


SELECT *
FROM db1.table_user AS USR
WHERE {{textInput1.value}} IS NULL OR USR.fullname LIKE '%' || {{textInput1.value}} || '%'
LIMIT 100;

Seems that you need to concatenate strings if you want the wildcard to be working. I got no idea if this is true or not.

The IS NULL OR part should return all results if textInput1 is empty. Otherwise you’re comparing fullname with an empty string I think.

Thanks for this :sweat_smile: questions like these make me look up stuff I never would otherwise and now I want to implement it myself in our app.

Edit
It works for me. % is voor multiple characters. But if you just want Thore and not Thoore than you should use Th_re as search value.

1 Like
3

Hi @Steven_W

Thenks for the help, I added || in the SQL also I see I have a function that removes all symbols.

WHERE 
 USR.FullNameCompr LIKE 
'%' || {{ fullname.value?.replace(/[^a-zA-Z0-9]/g, '') }} || '%' 
GROUP BY USR.ID

I see that "replace(/[^a-zA-Z0-9]/g)" removed my % from my text field.

my solutions

WHERE 
 USR.FullNameCompr LIKE 
'%' || {{ fullname.value?.replace(/[^a-zA-Z0-9%]/g, '') }} || '%' 
GROUP BY USR.ID
1 Like
4 
/[^a-zA-Z0-9%_]/g

:grimacing: if you want to use single character wildcard

1 Like