Select layer by attribute using SQL statement LEN() in ArcMap?

Question

I want to query the length of a string column and thought to use:

LEN([ColumnName]) > 5

However, that doesn't work at all. I'm querying a table in a fGDB in ArcMap 10.1, hence the notation of [ColumnName].

Can someone help me?

The error message I'm getting from ArcGIS is that I'm using an invalid SQL statement.

Answer 1

To calculate the length in characters of a string expression use CHAR_LENGTH(string_exp)

Field name delimiters for file geodatabase is double quotes (not square brackets).

So this should work:

CHAR_LENGTH("ColumnName") > 5

Here is a SQL reference for query expressions used in ArcGIS.

Answer 2

What if you add a new field to your table called, for example, "str_length" and then populate the field with the length of the value of the field you want?

str_length = len(columnname)

Then do your selection on the new calculated field.