Hello Community,
I want to filter on a column that in its rows contains a certain word of the phrase. For example "I want your" filter "want".
What syntax should I use and how should I write it in Sql?
Does it work with CONTAINS or LIKE?
I tried this, but it didn't work for me.
PAD uses access database engine drivers so whatever is supported by Access in a given version (check your drivers) - it is supported in PAD.
I do not remember if ILIKE is supported in any version but if not then just use
UCase([Column]) LIK UCASE 'value'
Hello community,
I have a query regarding SQL queries in power automate desktop where I just need a confirmation does ILIKE operator supports or doesn't support in power automate desktop(PAD).
Thank you
You are correct.
Got it to work now.
1 - Set a variable with value gmail.com
2 - Use the %%%{variableName}%%% on the SQL statement
e.g. '%%%email%%%'
Thanks again!
Okay so i think i need to do this in a litle more details.
If you insert string to WildCard it should be looking like this:
If you have the WildCard variable as an input variable:
It should be like this
Now when calling WildCard inside the SQL you need to do like this:
This is working.
So if you cant get it to work, you have an error somewhere on your part.
Nope. It didn't like the 3 % either
Getting the below error when setting the variable with 3 %
Yes. The script works if we ran it directly from SQL.
I typed an error in this answer.... You need to have the desired string typed with 3 % like this: %%%string%%% if you use variable.
Then you can call it inside the sql statement as normal LIKE '%variable%'.
This works because you escape the % by using double of them.
Try using 3 of them in the variable.
Should work.
Hi again 🙂
Well. have you tried the SQL statement directly in SQL Management studio?
And did you then get the wished result?
Have you tried doing exactly what i was doing in the answer?
Parsing gmail.com into a variable before using it in the SQL statement?
%WildCard% = %%gmail.com%%
(Should not matter at all if you do it like this or directly escape it in the SQL statement as you mention, but give it a try anyways...)
Finally, if you output the result SQL statement inside PowerAutomate does it then look as a valid SQL statement.
Maybe try outputting the SQL statement inside a variable and then use SQL Management Studio to test if it is even valid 🙂
Good luck
Here's a SELECT statement. If I escaped '%' with '%%', it will run and still list all records. It doesn't seem to filter the records.
SELECT a.SyCampusID,b.FirstName,b.LastName
FROM AdEnroll a (nolock)
inner join SyStudent b (nolock)
on a.SyStudentID = b.SyStudentId
inner join SySchoolStatus c (nolock)
on a.SySchoolStatusID = c.SySchoolStatusID
inner join SyCampus d (nolock)
on a.SyCampusID = d.SyCampusID
where a.SySchoolStatusID in ( 67, 68, 72, 103, 104, 106, 108, 70, 96, 107, 105, 109, 110 )
and DATEDIFF( day, GETDATE(), a.ExpStartDate ) < 63
and a.ExpStartDate between '10-24-2022' and '10-24-2022 11:59pm'
and a.AdShiftID = 18
and b.OtherEmail not like '%gmail.com%'
and b.email not like '%gmail.com%'
Since the method will work if used correctly, I think you need to reply with the SQL string that you are using, so that we can help you in the right direction.