SharePoint: Special characters in REST query filter statement

A few days back Sandeep mailed me about issues in SharePoint 2010 Autocomplete Lookup Field. The Autocomplete field was not working properly in some cases. When I looked at the test data it was evident that the issues appear only when there are special characters in the lookup data. Following are some of those test strings:
F&G - Washington
W + W WASSER und WARME GmbH
O'Callaghan Honey/Ray & Berndtson

Resolution:
Since Autocomplete Lookup Field uses REST to query the list, I fired the queries directly from the browser to see the results.
For F&G - Washington, I used this query:
http://demo2010a:90/_vti_bin/ListData.svc/Company/?$filter=startswith(Title,'F&G')
The result was HTTP 400 error.

Replacing & with %26 gave proper results
http://demo2010a:90/_vti_bin/ListData.svc/Company/?$filter=startswith(Title,'F%26G')

So, I realized that I needed to encode the strings before passing them to the query. This can be done in javascript by using encodeURIComponent, something like this:
var encodedTerm = encodeURIComponent(term);
Using this I was able to filter the second test string W + W WASSER und WARME GmbH, as well.
Now I tried to filter third test string, O'Callaghan Honey/Ray & Berndtson which contains apostrophe. encodeURIComponent does not encode apostrophe as it is a valid character in urls. But as I knew apostrophe is converted to %27 I tried to use that directly as shown:
http://demo2010a:90/_vti_bin/ListData.svc/Company/?$filter=startswith(Title,'O%27')
But this resulted in HTTP 400 error. Some more research in this revealed that if I use %27 or apostrophe twice it will give proper results as shown:
http://demo2010a:90/_vti_bin/ListData.svc/Company/?$filter=startswith(Title,'O%27%27')

http://demo2010a:90/_vti_bin/ListData.svc/Company/?$filter=startswith(Title,'O''')

Now getting back to Autocomple Lookup field, there was still one error , that is the JASON object which was returned by using the REST query escapes the apostrophe and hence was not properly parsed. The issue can be resolved by replacing the escape character "\" from the result by using dataFilter

dataFilter: function (data, type){
return data.replace(/\\'/g,"'");
}

Following is the final piece of code for autocomplete field:

.autocomplete({
source: function( request, response ) {
var restServiceAddress = siteUrl + "/_vti_bin/ListData.svc/";
var term = extractLast( request.term );
var encodedTerm = encodeURIComponent(term);
if (encodedTerm.indexOf("'") >=0)
{    
 encodedTerm = encodedTerm.replace(/'/g, "''");
}
var requestUrl = restServiceAddress + listName<%=autocomplete.ClientID%> +"()" + "?$filter=startswith("+fieldName<%=autocomplete.ClientID%>+",'" + encodedTerm + "') <%=Filter%>&$select="+fieldName<%=autocomplete.ClientID%>+",Id"; 
$.ajax({
          url: requestUrl,
          dataType: "json",
          dataFilter: function (data, type){
                      return data.replace(/\\'/g,"'");
                      },
         success: function (data) {
                  response(jQuery.map(data.d.results, function (suggestion) {     
                 availableIDs<%=autocomplete.ClientID%>.push(suggestion[fieldName<%=autocomplete.ClientID%>] + "[" + suggestion.Id + "]");
         return suggestion[fieldName<%=autocomplete.ClientID%>];
                           }))
                            },
         error: function (XMLHttpRequest, textStatus, errorThrown) {
                                alert(XMLHttpRequest.responseText);
                            }
                          });
   }

SharePoint: Special characters in REST query filter statement

Share this