Handling quotes single and double in filter expressions

I am not clear on how to handle " and ’ in a filter expression. When a text string is read in via an Excel read range into a datatable a " is doubled to “” which I think is what you would expect, however a single ’ is not altered and remains a single '. When that field value is passed into a filter expression it then converts the “” into " and does nothing with the single '. The filter then fails I assume because it thinks the single ’ is the literal delimiter. Does anyone have experience of using text strings which have quotes in them for lookup purposes and how to handle this?

Quotes are escaped using "". So string "Hello ""Sir""", refers to Hello, "Sir". Single quotes do not need to be escaped.

The issue I have though is that I am using a datatable.select using a filter expression and there are single quotes in the string which throws an error, because the Select syntax interprets the single quote as a delimiter rather than a single quote in a string, e.g. if filtering a column with the value “[colname] = ‘d’Angelo’” it will interpret the single quotation mark in d’ as the delimiter. This is easily resolved if the value is a literal, but if it is a string passed in through a variable I am not sure how to deal with this.

I found the solution on StackOverflow. Use string.replace on the string assigned to the filter expression to double up the single quotes. This then corrects the syntax for the dt.select and allows it to correctly read the string.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.