nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using SQL CAST functionality in against a File Geodatabase

DominickCisson

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?

Find more posts tagged with

Accepted answers

All comments

JoshuaBixby

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) + "%'"

dougbrowning

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

dougbrowning

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

dougbrowning

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

dougbrowning

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

DominickCisson

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

LanceShipman

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.