Query where clause type conversion

[TedChapin]

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

[JenniferNery]

Is your Query.Where clause checking against two different fields? If so, I think you need to keep the search fields separate.

For example:
Where = Street_Number = 5 and Street_Name = 'Main Street'

int streetNumber;
string streetName;
query.Where = string.Format("StreetNumber = {0} and Street_Name = '{1}'", streetNumber, streetName);

[TedChapin]

Yes, the address number and street name are in separate fields.  But I want to keep my UI googlish with only 1 input box.  I actually simplified the query for my post.  I search for MBL, owner name, and address with 1 user text box.  When the MBL, owner name, street number, and street name fields are all string, it works with this query:

query.Where = String.Format("UPPER(MBLU) LIKE '%{0}%' OR UPPER(Owner) LIKE '%{0}%' OR UPPER(Co_owner) LIKE '%{0}%' OR CONCAT(Street_Number, CONCAT(' ', Street_Name)) LIKE '%{0}%'", SearchText.Text.ToUpper)

But one town I work with has a numeric field type for the street number.  SO, I would like to do something like this:

query.Where = String.Format("UPPER(MBLU) LIKE '%{0}%' OR UPPER(Owner) LIKE '%{0}%' OR UPPER(Co_owner) LIKE '%{0}%' OR CONCAT(CAST(Street_Number AS varchar()), CONCAT(' ', Street_Name)) LIKE '%{0}%'", SearchText.Text.ToUpper)

But CAST (or at least the varchar data type) doesn't seem to be supported by whatever version of SQL the REST API uses.  Neither does CONVERT.  The documentation doesn't go into any detail about what SQL statements are allowed.  I just guessed about CONCAT and UPPER.

It may be that, in order to keep my search UI to one text box to search all those fields, I will have to modify the table schema to make the street number field a string.

[JenniferNery]

I don't think UPPER or CONCAT works either. Have you tried doing this query on your web browser from the service's REST endpoint?

I guess what you can do is split the input string and try to determine which can be converted to a number so you can check them against your number fields when you form your where clause.

[TedChapin]

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.

[JenniferNery]

According to REST API http://help.arcgis.com/en/arcgisserver/10.0/apis/rest/query.html

Any 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]

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.