SQL SYNTAX CONTAINS OR LIKE

(0) Share

Report

Posted on by fgonzalez515

296

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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (15)

Answers (2)

momlo 1,527 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Manju_99
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'

Was this reply helpful? Yes No
Manju_99 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
dchen 19 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
dchen 19 on at

Like (0)

Report
Copy link

Link copied!

Nope. It didn't like the 3 % either

Getting the below error when setting the variable with 3 %

Was this reply helpful? Yes No
dchen 19 on at

Like (0)

Report
Copy link

Link copied!

Yes. The script works if we ran it directly from SQL.

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
dchen 19 on at

Like (0)

Report
Copy link

Link copied!

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%'

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No