Skip to main content

Notifications

Power Automate - Power Automate Desktop
Answered

SQL SYNTAX CONTAINS OR LIKE

(0) ShareShare
ReportReport
Posted on by 290

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.

 

fgonzalez515_0-1661924114986.png

 

 

 

  • momlo Profile Picture
    momlo 1,527 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

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

  • Manju_99 Profile Picture
    Manju_99 2 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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

  • dchen Profile Picture
    dchen 19 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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!

  • Mibsk Profile Picture
    Mibsk 49 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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: 

    Mibsk_0-1666329353418.png

    If you have the WildCard variable as an input variable:

    Mibsk_2-1666329481008.png


    It should be like this

     

    Mibsk_1-1666329456265.png

    Now when calling WildCard inside the SQL you need to do like this: 

    Mibsk_3-1666329537837.png


    This is working. 
    So if you cant get it to work, you have an error somewhere on your part. 

     

  • dchen Profile Picture
    dchen 19 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    Nope. It didn't like the 3 % either

     

    Getting the below error when setting the variable with 3 %

     

    dchen_0-1666287662680.png

     

  • dchen Profile Picture
    dchen 19 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

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

  • Mibsk Profile Picture
    Mibsk 49 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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.

  • Mibsk Profile Picture
    Mibsk 49 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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 

  • dchen Profile Picture
    dchen 19 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

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

  • Mibsk Profile Picture
    Mibsk 49 on at
    Re: SQL SYNTAX CONTAINS OR LIKE

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard