How do you query a text string that contains an apostrophe?

MikeOnzay · ‎01-27-2012

I have a featureclass with a value (type: text) that contains an apostrophe (ex: Red's Reef). The value is being passed into the query function. When I query for that string nothing happens and I get no error in firebug. Using console log I can see that the value is being passed in properly and I can see that the query.where value is correct too.

I can see in ArcMap and the Services directory that including a second apostrophe results in a successful search.

I have tried to add a second apostrophe or using escape characters in the original value (it starts out in json text, gets pulled into a drop down menu, and then gets passed on to the query function) but nothing seems to work.

The easiest thing for me to do is remove the apostrophe for the field value itself but I'm changing the official spelling of this place and I would rather not do that. I'm curious to know if there is a workaround.

KellyHutchins · ‎01-27-2012

Here's one approach that uses a regular expression to replace a single quote with two single quotes:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html>   <head>     <meta http-equiv="Content-Type" content="text/html; charset=utf-8">     <meta http-equiv="X-UA-Compatible" content="IE=7,IE=9" />     <!--The viewport meta tag is used to improve the presentation and behavior of the samples        on iOS devices-->     <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no"/>     <title>Topographic Map</title>     <link rel="stylesheet" type="text/css" href="http://servicesbeta.esri.com/jsapi/arcgis/2.7/js/dojo/dijit/themes/claro/claro.css">     <style>       html, body { height: 100%; width: 100%; margin: 0; padding: 0; }       .esriScalebar{         padding: 20px 20px;       }       #map{         padding:0;       }     </style>     <script type="text/javascript">var djConfig = {parseOnLoad: true};</script>     <script type="text/javascript" src="http://servicesbeta.esri.com/jsapi/arcgis/?v=2.7"></script>     <script type="text/javascript">       dojo.require("dijit.layout.BorderContainer");       dojo.require("dijit.layout.ContentPane");       dojo.require("esri.map");       dojo.require("esri.layers.FeatureLayer");               var map;       var fl;               function init() {         var initExtent = new esri.geometry.Extent({"xmin":-9092108.81,"ymin":3675025.71,"xmax":-8996638.96,"ymax":3716454.57,"spatialReference":{"wkid":102100}});         map = new esri.Map("map",{           extent:initExtent         });         //Add the topographic layer to the map. View the ArcGIS Online site for services http://arcgisonline/home/search.html?t=content&f=typekeywords:service             var basemap = new esri.layers.ArcGISTiledMapServiceLayer("http://server.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer");         map.addLayer(basemap);         var infoTemplate = new esri.InfoTemplate("Attributes", "${*}");          fl = new esri.layers.FeatureLayer('http://egisws02.nos.noaa.gov/ArcGIS/rest/services/NMFS/HAPC/MapServer/0',{             mode:esri.layers.FeatureLayer.MODE_SELECTION,             outFields:['HAPC_Sitename'],             infoTemplate:infoTemplate         });           var r = Math.floor(Math.random() * 250);         var g = Math.floor(Math.random() * 100);         var b = Math.floor(Math.random() * 100);              var symbol= new esri.symbol.SimpleFillSymbol(esri.symbol.SimpleFillSymbol.STYLE_SOLID,new esri.symbol.SimpleLineSymbol(esri.symbol.SimpleLineSymbol.STYLE_SOLID,new dojo.Color([r,g,b,0.9]), 4),new dojo.Color([r,g,b,0.5]));       fl.setSelectionSymbol(symbol,esri.layers.FeatureLayer.SELECTION_NEW,function(sel){         console.log(sel.length);       });         map.addLayer(fl);          dojo.connect(map, 'onLoad', function(theMap) {            //resize the map when the browser resizes           dojo.connect(dijit.byId('map'), 'resize', map,map.resize);         });       }         function selectFeatures(){                      var query = new esri.tasks.Query();             var locValue = dojo.byId('siteName').value;             query.where = "HAPC_Sitename = " + "'"  +  locValue.replace("'", "''")    + "'" ;              fl.selectFeatures(query);           }                 dojo.addOnLoad(init);     </script>   </head>      <body class="claro">     <div dojotype="dijit.layout.BorderContainer" design="headline" gutters="false" style="width: 100%; height: 100%; margin: 0;">        <div id='header' dojotype='dijit.layout.ContentPane' region='top' style='height:25px;'>             <input type='button' value='Select' onClick='selectFeatures();'/>             <input type='text' id='siteName' value="Gray's Reef National Marine Sanctuary"/>        </div>       <div id="map" dojotype="dijit.layout.ContentPane" region="center" style="border:1px solid #000;padding:0;">      </div>     </div>   </body>  </html>

View solution in original post

KellyHutchins · ‎01-27-2012

Here's one approach that uses a regular expression to replace a single quote with two single quotes:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html>   <head>     <meta http-equiv="Content-Type" content="text/html; charset=utf-8">     <meta http-equiv="X-UA-Compatible" content="IE=7,IE=9" />     <!--The viewport meta tag is used to improve the presentation and behavior of the samples        on iOS devices-->     <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no"/>     <title>Topographic Map</title>     <link rel="stylesheet" type="text/css" href="http://servicesbeta.esri.com/jsapi/arcgis/2.7/js/dojo/dijit/themes/claro/claro.css">     <style>       html, body { height: 100%; width: 100%; margin: 0; padding: 0; }       .esriScalebar{         padding: 20px 20px;       }       #map{         padding:0;       }     </style>     <script type="text/javascript">var djConfig = {parseOnLoad: true};</script>     <script type="text/javascript" src="http://servicesbeta.esri.com/jsapi/arcgis/?v=2.7"></script>     <script type="text/javascript">       dojo.require("dijit.layout.BorderContainer");       dojo.require("dijit.layout.ContentPane");       dojo.require("esri.map");       dojo.require("esri.layers.FeatureLayer");               var map;       var fl;               function init() {         var initExtent = new esri.geometry.Extent({"xmin":-9092108.81,"ymin":3675025.71,"xmax":-8996638.96,"ymax":3716454.57,"spatialReference":{"wkid":102100}});         map = new esri.Map("map",{           extent:initExtent         });         //Add the topographic layer to the map. View the ArcGIS Online site for services http://arcgisonline/home/search.html?t=content&f=typekeywords:service             var basemap = new esri.layers.ArcGISTiledMapServiceLayer("http://server.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer");         map.addLayer(basemap);         var infoTemplate = new esri.InfoTemplate("Attributes", "${*}");          fl = new esri.layers.FeatureLayer('http://egisws02.nos.noaa.gov/ArcGIS/rest/services/NMFS/HAPC/MapServer/0',{             mode:esri.layers.FeatureLayer.MODE_SELECTION,             outFields:['HAPC_Sitename'],             infoTemplate:infoTemplate         });           var r = Math.floor(Math.random() * 250);         var g = Math.floor(Math.random() * 100);         var b = Math.floor(Math.random() * 100);              var symbol= new esri.symbol.SimpleFillSymbol(esri.symbol.SimpleFillSymbol.STYLE_SOLID,new esri.symbol.SimpleLineSymbol(esri.symbol.SimpleLineSymbol.STYLE_SOLID,new dojo.Color([r,g,b,0.9]), 4),new dojo.Color([r,g,b,0.5]));       fl.setSelectionSymbol(symbol,esri.layers.FeatureLayer.SELECTION_NEW,function(sel){         console.log(sel.length);       });         map.addLayer(fl);          dojo.connect(map, 'onLoad', function(theMap) {            //resize the map when the browser resizes           dojo.connect(dijit.byId('map'), 'resize', map,map.resize);         });       }         function selectFeatures(){                      var query = new esri.tasks.Query();             var locValue = dojo.byId('siteName').value;             query.where = "HAPC_Sitename = " + "'"  +  locValue.replace("'", "''")    + "'" ;              fl.selectFeatures(query);           }                 dojo.addOnLoad(init);     </script>   </head>      <body class="claro">     <div dojotype="dijit.layout.BorderContainer" design="headline" gutters="false" style="width: 100%; height: 100%; margin: 0;">        <div id='header' dojotype='dijit.layout.ContentPane' region='top' style='height:25px;'>             <input type='button' value='Select' onClick='selectFeatures();'/>             <input type='text' id='siteName' value="Gray's Reef National Marine Sanctuary"/>        </div>       <div id="map" dojotype="dijit.layout.ContentPane" region="center" style="border:1px solid #000;padding:0;">      </div>     </div>   </body>  </html>

DavidWilton · ‎03-26-2014

I found that if I had a query which had more than one clause to it the query would fail no matter if the apostrophes were escaped. For example:

where = FIELD_NAME = '''myName" OR FIELD_NAME = '''theName'

will fail using the query task

The only way it will work is if I go via a proxy.