topic Re: Using SQL CAST functionality in against a File Geodatabase in Data Management Questions

Using SQL CAST functionality in against a File Geodatabase

DominickCisson — Thu, 19 Apr 2012 15:53:32 GMT

There are a number of references in ESRI's documentation (http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=SQL_reference) and other threads (http://forums.esri.com/Thread.asp?c=159&f=1707&t=280410) that seem to indicate that one can use the SQL "CAST" function in a where statement against a file geodatabase to convert field types on the fly. I'm trying to pull records based on a set of integers that begin with a particular number. To do so, I should cast the integer field to a character, and then perform a LIKE statement against the field. By all rights, and per documentation, the following statement should work against a File GDB: CAST("Price" AS CHARACTER) LIKE '1%' But... it does not. It doesn't work in ArcMap 10, and it doesn't work in 9.3 What am I doing wrong here?

Re: Using SQL CAST functionality in against a File Geodatabase

LanceShipman — Thu, 19 Apr 2012 16:19:54 GMT

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.

Re: Using SQL CAST functionality in against a File Geodatabase

DominickCisson — Thu, 19 Apr 2012 16:49:28 GMT

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.

Hallelujah! I tried every combination I could think of, including "varchar" with a width, but I never put a width on "character". It's always fun navigating the vagaries of SQL across SQL Server, File GDB's and Personal GDB's

Thanks

Re: Using SQL CAST functionality in against a File Geodatabase

DougBrowning — Wed, 03 Jul 2019 19:56:08 GMT

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

Re: Using SQL CAST functionality in against a File Geodatabase

DougBrowning — Wed, 03 Jul 2019 20:11:27 GMT

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

Re: Using SQL CAST functionality in against a File Geodatabase

DougBrowning — Wed, 03 Jul 2019 21:36:57 GMT

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

Re: Using SQL CAST functionality in against a File Geodatabase

JoshuaBixby — Mon, 08 Jul 2019 21:50:44 GMT

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?

Re: Using SQL CAST functionality in against a File Geodatabase

DougBrowning — Mon, 08 Jul 2019 22:15:12 GMT

It works in a GDB but does not work when hitting a in_memory set in Python. Says invalid SQL statement.

thanks

Re: Using SQL CAST functionality in against a File Geodatabase

JoshuaBixby — Tue, 09 Jul 2019 15:22:20 GMT

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.

Re: Using SQL CAST functionality in against a File Geodatabase

DougBrowning — Tue, 31 Mar 2020 19:37:37 GMT

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

Re: Using SQL CAST functionality in against a File Geodatabase

JoshuaBixby — Tue, 31 Mar 2020 20:37:21 GMT

You will need to build and apply the definition query outside of the GUI, e.g., ArcPy layer.definitionQuery. The following worked for me when testing:

tbl.definitionQuery = "SpeciesWoody LIKE '%" + chr(9) + "%'"