Using a value with a single quote as an Odata filter query element

[BenGrady]

Hello,

I am attempting to Get Items from a SharePoint list where the value of a particular column is equal to the Department of the user who created the item that initiated the Flow. The problem is that many of our departments include a single quote in them (e.g. Men's Guest Services). When the Odata query hits that single quote it throws an error because single quotes need to be escaped with a second single quote. Unfortunately there is currently no way to do string replacement in Flow to the best of my knowledge. 

Any ideas on what to do (short of changing all our department names)?

Thanks,

Ben

[v-micsh-msft]

Hi @BenGrady,

Single quote in OData Query need to present in pairs.

Which means if your string contains one single quote, we need to "double" it.

For example, the string we would like to use under Filter Query with the field Department is:

Men's Guest Services

Then the formula should be:

Department eq 'Men''s Guest Services'

Check the following screenshots:

Regards,

Michael

[BenGrady]

Hi @v-micsh-msft,

Thank you for your reponse!

The issue I am running into is that the Department field is a dynamic value. That means that I would need to have a string manipulation procedure in order to insert that extra single quote. Do you know of a string manipulation for dynamic values in Flow?

Thanks,

Ben

[automaton]

@BenGrady  assuming you got to the bottom of this but if not you should be able to use the replace() function for this. 

To replace a single quote in an dynamic OData Query String value where the example field is Department.

replace(items('Apply_to_each')?['Department']?['Value'],'''','''''')

It looks a bit funky but the '''' in the second part of the replace is required to escape the ' in the input string to the replace function. So essentially this is matching on '. And then similarly, the '''''' is replacing the single quote with two single quotes. It should work as you expect.

Hope this helps for the future.

[niclasdahl]

I have a similar problem. I have a flow that uses the 'Get Files' action with a filter.  purpose is to check if a file already exist in a folder, if it does it should fetch the properties of that file.

Problem is a that i use the dynamic content 'file name with extension' to filter, and if the file name contains a single apostrophe, it will cause the filter to fail, and say that the expression isnät valid.... since i can't go about changing the name of all files that have a single apostrophe in them, is there another workaround for this?

The above will result in this:

"message": "The expression \"Title eq 'XXX0000-0-xxxx'x xx xxxx.pdf'\" is not valid.\r\nclientRequestId:...

Is it possible to circumvent this issue by putting the dynamic content within brackets or using quotations instead of single apostrophes?

[automaton]

Hi @niclasdahl ,

As mentioned above, try using an expression like this to escape the quotes in the merged filename, instead of a direct merge of the filename:

replace(triggerBody()?['{FilenameWithExtension}'],'''','''''')

 Hope this helps.

[Symira]

I need to query records from CDs entity that have an quote sign "'" in a field. In Sql I could easily do a LIKE filter. EG.
Select * from my table where filter field LIKE ' %'' %'.

How to do that in a Flow?