[GIS] Query for numeric field against text field that contains a numeric value

arcgis-10.2arcgis-desktoppythonsql

In my database, I have one field, Service_Day, formatted as numeric, which contains a single digit number 1 through 7 (for days of week). I want to compare it against another field, called Region, formatted as Text/String which also contains a single alphanumeric character 1 through 7 (again representing days of the week). It seems that I should be able to query the records where "Service_Day" <> "Region", by using the proper syntax. I thought AsString or AsNumber or Convert might be appropriate, but can't figure it out. Any ideas, other than creating a new field and formatting it as either text or numeric and then copying whichever data to that field?

Best Answer

The answer to your question depends on the database you are working with. For File Geodatabase feature classes and tables you can use the Cast operator. For example, this will work in a File Geodatabase:

Service_Day <> CAST(Region AS FLOAT)

You can also convert a numeric value to a string and compare it:

CAST(Service_Day AS VARCHAR(1)) <> Region

Converting to a string is often most useful when used in combination with the SUBSTRING function:

SUBSTRING(CAST(OBJECTID AS VARCHAR(50)) FROM 3 FOR 1) <> Region

(Not a particularly great real life example, but the syntax works).

Nothing I have tried works for Shapefiles.

Related Question