Hello, I'm missing something obvious. This simple SQL query works fine:
SELECT firstName,lastName,email FROM databasename where LOWER(lastName) like '%fisher%' LIMIT 1000
But when I try and pass in a textInput value from my form, it returns null.. despite the variable showing it contains the proper value.
SELECT firstName,lastName,email FROM databasename where LOWER(lastName) like '%{{textInput1.value}}%' LIMIT 1000
Any help appreciated before all hair is pulled out of my head. Thanks.
Hello,
Your BQ resource has Prepared Statement on - this is default setting.
With it on, you need to put the % within the {{ }}
Outside will not work
Prepared statement will put quotes around the value from {{ }} so your SQL will be along the line of '%''isher''%' Meaning it's query for like 'isher' (quotes in the value)
% inside {{ }} will work. Note: I'm not using text input, you just need to append the % to the end of your input within {{ }}
Thank you! that's what I needed.. final result: SELECT firstName,lastName,email FROM databasename where LOWER(lastName) LIKE {{"%" + textInput1.value +"%"}} LIMIT 1000
Hi @Gear_Fisher, why not use REGEXP_CONTAINS(lastName, {{"(?i)"+textInput1.value}} )?
The added string (?i) is a flag to search for any cases (upper or lower). Whenever I'm using BQ, REGEXP_CONTAINS is much more powerful than LIKE.
