2

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?

  • 2
    Would you be able to edit your question to indicate the GIS software and version that you are using, please? – PolyGeo Dec 10 '14 at 21:42
  • 1
    int() or round() might be your friend here but without knowing what software, what data type (shape file, PostGIS..) and some example values we can't help much. – Michael Stimson Dec 10 '14 at 21:50
  • I am using ArcGIS 10.2.2 – Wally Kempf Dec 10 '14 at 22:38
2

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.

  • Thank you! Either of the first 2 expressions worked perfectly for me. – Wally Kempf Dec 12 '14 at 12:41
  • I would like to point out a slight difference in the behavior of the two expressions. If the first syntax is used all string values that are non-numeric will not be selected. For the second syntax, all string field values that are non-numeric will be selected. So with syntax 1 a string of "A" will not be selected for any numeric field value, because "A" cannot be converted to a number, but with syntax 2 a string field value of "A" will be selected for every numeric field value, because all numeric values can be converted to strings and no numeric string equals a non-numeric string. – Richard Fairhurst Dec 12 '14 at 15:24
0

A lot of VBscript functions work in SQL.. for example CDbl but such commands are for the more modern databases like Microsoft Jet (Access format), Esri fGDB and enterprise databases like Oracle, SQL Server and PostgreSQL.

Shapefiles rely on long outdated DBase IV commands which can be really difficult to get help on. This is because queries are passed on to the database driver, but even shapefiles seem to have a subset of this SQL... Arccording to documentation VAL(TEXT) should make it into a number, STR( [, [, [, ]]]) should convert numbers to text but that doesn't work.

As a workaround try adding a numeric field and calculate the values, then when converted to a number you can use the values in SQL.

in VB: CDBL(Text_Field).
in python: float(!Text_Field!)

If your text contains invalid characters or is empty then you'll have problems with a straight translation, a simple try/except should step over that:

enter image description here

Copyable code here:

def MakeThisTextNumeric(TheText):
  try:
    return float(TheText)
  except:
    return 0
  • Thank you! I am going to hang onto this for future reference. – Wally Kempf Dec 12 '14 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.