Use:
CAST("Price" AS CHARACTER(12)) LIKE '1%'
I need to add this to the doc. The width of the character field must be set. It's consistent with other SQL implementations, but it's not obvious.
What about for DOUBLE or INT? I can never get cast to work for numbers and had given up till I saw this post.
CAST(RockFragments AS DOUBLE) is a no go
Ok finally got it. You need to use NUMERIC and give it a size. You also need the field name in "".
So this worked
CAST("RockFragments" AS NUMERIC(38,9)) <> (CAST("ESD_FragVolPct" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct2" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct3" AS NUMERIC(38,9)))
This help says you can use double but it never works. Crazy how bad this help page is.
SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop
Edit:
This worked great in a ArcMap Def Query but bombed in a search cursor in python with invalid SQL statement.
whereClause = 'CAST("RockFragments" AS NUMERIC(38,9)) <> (CAST("ESD_FragVolPct" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct2" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct3" AS NUMERIC(38,9)))'
It is hitting a in_memory table so that may be it. I have found that in_memory sets do not support all the SQL that geoDBs do. Which is not documented or anything. This may be what stopped me from cast in the past. Bummer.
Any ideas?
thanks
Yep found my own old post about it. I was hoping it was the numeric issue.
https://community.esri.com/thread/218020-cast-does-not-work-in-searchcursor-queries
Doug Browning, I am confused by this last comment. Are you working with in-memory geodatabases again and CAST isn't working? Or, are you working with file geodatabases and CAST isn't working?
It works in a GDB but does not work when hitting a in_memory set in Python. Says invalid SQL statement.
thanks
Ah, OK, now I get what you are saying. Yes, in-memory workspaces in ArcMap do not support all the SQL statements that file geodatabases support. I have not experimented yet with the new "memory" workspace in Pro, but I suspect it will also have even more limited SQL support.
UPDATE: Doug Browning, good news, I just checked Pro and the new "memory" workspace works with SQL CAST while the older "in_memory" workspace does not.
Ok I have a new one today that I cannot find any info on.
Using a GDB.
I need to a Definition Query for tab \t.
This (also tried CHAR)
SpeciesWoody LIKE '%' || CHR(9) || '%'
or this should work but it is not
SpeciesWoody LIKE '%' + CHR(9) + '%'
Also tried looking for a \ using
'%\\%'
Anyone have any ideas?
thanks