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)

Verified answer

Mibsk 49 on at

Like (0)

Report

Hi 🙂

I made a variable that contains the expression like this:
%WildCard% = %%something%%

Then i do this in the sql string:
......AND json_data LIKE '%WildCard%'

Was this reply helpful? Yes No
fgonzalez515 296 on at

Like (0)

Report

Hi @Mibsk,

Thank you, got a good result.

Can you help me with the following?

When I write the syntax individually, I get a result.

But when I write the syntax together, I get no results

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report

Its hard to 100% tell when i cant test against your sql server.
But it seem like you have " # " where you should have " ' "
#%FormattedDateTime%# looks like it should be '%FormattedDateTime%' etc.

Was this reply helpful? Yes No
fgonzalez515 296 on at

Like (0)

Report

When I made that change it gave me an error.

In SQL Instruction, from row 4 to 6, the filter does not work for me

Was this reply helpful? Yes No
Verified answer

dchen 19 on at

Like (0)

Report

When using the method of adding
%%something%% to the %wildcard% variable, it doesn't work. The SQL didn't give an error, but the results are wrong.

Was this reply helpful? Yes No
Mibsk 49 on at

Like (0)

Report

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
dchen 19 on at

Like (0)

Report

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

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
Mibsk 49 on at

Like (0)

Report

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
dchen 19 on at

Like (0)

Report

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

Was this reply helpful? Yes No