Select By Attributes

[JasonBagwell1]

Hello,

I'm working in a feature class over 144,000 address points. And, in the !street number! field a lot of the addresses include a letter indicating an apartment.  For example,  1001U and 85b,  both lower and Uppercase.  

Is there a simple SQL expression, or another way to select only those addresses, all of them at once?

Any thoughts, or advice would be greatly appreciated.

Thanks,

Jason

[AllenDailey1]

Hello,

I'm not an expert in SQL, but I looked around a little and found something that worked for me when I tested it.  It's the last comment on this page: https://ask.sqlservercentral.com/questions/1743/how-to-select-records-with-letters-in-an-nvarchar.ht...

It looks like this in ArcGIS Pro, select by attributes:

You could add another clause for lower case, as in the last comment on that website above.

I hope that helps!

[JasonBagwell1]

Thanks for the reply.  I tried your suggestion, but unfortunately it's not working for me.

[AllenDailey1]

How about if you use "Contains the text" instead of "is equal to," in the middle drop-down?

[JasonBagwell1]

Same thing I'm afraid, but thanks for trying to help.

[JohannesLindner]

The SQL that is allowed in Select Layer By Attributes is quite limited. For example, it can't handle the regex-like queries for "[A-Za-z]" that would search for values containing any letter in "normal" SQL.

What you can do:

CAST(YourField AS INTEGER) >= 0

and then invert the selection

The SQL expression will only select values it can convert from string to integer (so all values that don't contain letters), and when you invert that, you get all values containing letters:

[JasonBagwell1]

Johannes, thanks for the suggestion.  I've tried it, but I get an 'error' message.  Anything you notice that could be the issue.  Thanks again.

[JohannesLindner · Accepted solution]

I tested that expression in a File GDB, but it doesn't seem to work in a shape file. If you're working in an Enterprise GDB, your DBMS also might not be able to handle the expression.

Instead of going back and forth trying to find the right expression for your DBMS, it might be easier to just calculate a new field:

# Calculate Field
# Field type: Short or Long
# Language: Python 3


# Field =
re.search(".*[A-Za-z].*", !STREETNUM!) is not None

# Code Block
import re

This new field will be 1 if there is a letter in STREETNUM, so you can select by that field:

[JasonBagwell1]

Thanks Johannes,

That works, and will save me a lot of extra work.  I appreciate the help.