Query where clause type conversion

Question

I want to do a type conversion and a concatenation in a query where clause.  I have street number as a esriFieldTypeInteger and street name as a esriFieldTypeString.  I want to search for "5 Main Street" as a single search term.  If the street number were string, I could use this: 
CONCAT(street_num, CONCAT(' ', street_name)) = '5 Main Street'
But the numeric field type is causing difficulty.  I've tried CAST and CONVERT to varchar, nvarchar, text, but it doesn't work.

Any ideas?

Ted Chapin

TedChapin · Answer

Yes, CAST does work.  I had been using CAST(street_num AS varchar()), which is a lazy way to convert to string if you don't care about the string's length.  In T-SQL the default is 30 if it's not specified.  The REST API won't process varchar(), but this works: varchar(5), or whatever length is appropriate.

JenniferNery · Answer

According to REST API http://help.arcgis.com/en/arcgisserver/10.0/apis/rest/query.htmlAny legal SQL where clause operating on the fields in the layer is allowed.

According to this http://msdn.microsoft.com/en-us/library/ms187928.aspx, you can construct a CAST the following way: AV_PID = CAST('1069' as INT)

TedChapin · Answer

Those SQL statements work.  Try this query:UPPER(MBLU) LIKE '%106 LEDGE RD%' OR UPPER(Grantee) LIKE '%106 LEDGE RD%' OR UPPER(Co_grantee) LIKE '%106 LEDGE RD%' OR CONCAT(Street_Number, CONCAT(' ', Street_Name)) LIKE '%106 LEDGE RD%'

on this table:https://eis.woodardcurran.com/ArcGIS/rest/services/PublicServices/Ogunquit/MapServer/22/query

Wish there was some documentation about what the REST query's where clause supports.