How to handle single quotes in the filter property with Get Items actions

%3CLINGO-SUB%20id%3D%22lingo-sub-2617747%22%20slang%3D%22en-US%22%3EHow%20to%20handle%20single%20quotes%20in%20the%20filter%20property%20with%20Get%20Items%20actions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2617747%22%20slang%3D%22en-US%22%3E%3CP%3EIf%26nbsp%3B%20you%20are%20using%20the%20SharePoint%20Get%20Items%20actions%20and%20using%20the%20Odata%20Filter%20Query%20syntax%2C%20then%20you%20need%20to%20handle%20single%20quotes%20in%20your%20filter%20syntax.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20a%20single%20quote%20is%20a%20special%20character%2C%20you%20need%20to%20use%20another%20special%20character%20to%20%22escape%22%20it.%20This%20lets%20the%20system%20know%20that%20the%20next%20character%20should%20be%20treated%20as%20a%20regular%20character%20rather%20than%20what%20it%20usually%20means.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20languages%2C%20the%20escape%20character%20could%20be%20a%20backslash%2C%20however%20in%20Power%20Automate%2C%20the%20escape%20character%20is%20a%20single%20quote.%20So%2C%20to%20escape%20a%20single%20quote%2C%20you%20need%20to%20type%20two%20single%20quotes.%20We%20can%20use%20the%20replace%20function%20to%20perform%20this%20opearation%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20have%20a%20look%20at%20a%20standard%20replace%20function%20first%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20look%20for%20any%20occurrence%20of%20'this'%20in%20my%20Filename%20variable%20and%20replace%20with%20'that'.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20here's%20what%20it%20looks%20like%20when%20we%20want%20to%20replace%20single%20quotes%20with%20two%20single%20quotes%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-08-06_8-59-55.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300983iFDFD2F7D1E1C39D1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222021-08-06_8-59-55.png%22%20alt%3D%222021-08-06_8-59-55.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20text%20version.%20note%20that%20there%20are%20no%20double%20quotes%20here%2C%20only%20multiple%20single%20quotes%3A%3C%2FP%3E%3CP%3EFileLeafRef%26nbsp%3Beq%26nbsp%3B'%3CI%3Ereplace(variables('FileName')%2C%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E%2C%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E'%3C%2FI%3E%3CI%3E)%3C%2FI%3E'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20explanation%20of%20the%20colour%20coding%3A%3C%2FP%3E%3COL%3E%3CLI%3EEverything%20between%20the%20'green%20quotes'%20is%20a%20Power%20Automate%20expression%2C%20not%20plain%20text%3C%2FLI%3E%3CLI%3EThe%20'yellow%20quotes'%20are%20normal%20quotes%20for%20wrapping%20strings%2C%20e.g.%20replace(myVariable%2C'this'%2C%20'that')%3C%2FLI%3E%3CLI%3EThe%20'light%20blue%20quotes'%20are%20there%20to%20escape%20the%20following%20character%20(a%20single%20quote)%3C%2FLI%3E%3CLI%3EThe%20pink%20quote%20(%3CI%3E'%3C%2FI%3E)%20is%20the%20character%20to%20find%3C%2FLI%3E%3CLI%3EThe%20two%20orange%20quotes%20(%3CI%3E'%3C%2FI%3E)%20are%20the%20characters%20to%20replace%20the%20found%20text%20with%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2617747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

If  you are using the SharePoint Get Items actions and using the Odata Filter Query syntax, then you need to handle single quotes in your filter syntax.

 

Since a single quote is a special character, you need to use another special character to "escape" it. This lets the system know that the next character should be treated as a regular character rather than what it usually means.

 

In other languages, the escape character could be a backslash, however in Power Automate, the escape character is a single quote. So, to escape a single quote, you need to type two single quotes. We can use the replace function to perform this opearation

 

Let's have a look at a standard replace function first:

2021-08-06_9-08-33.png

This will look for any occurrence of 'this' in my Filename variable and replace with 'that'. 

 

Now, here's what it looks like when we want to replace single quotes with two single quotes:

2021-08-06_8-59-55.png

 

Here is the text version. note that there are no double quotes here, only multiple single quotes:

FileLeafRef eq 'replace(variables('FileName'),'''','''''')'

 

Here is an explanation of the colour coding:

  1. Everything between the 'green quotes' is a Power Automate expression, not plain text
  2. The 'yellow quotes' are normal quotes for wrapping strings, e.g. replace(myVariable,'this', 'that')
  3. The 'light blue quotes' are there to escape the following character (a single quote)
  4. The pink quote (') is the character to find
  5. The two orange quotes (') are the characters to replace the found text with
0 Replies