topic Re: Select By Attributes in ArcGIS Pro Questions

Select By Attributes

JasonBagwell1 — Thu, 27 Jul 2023 17:52:07 GMT

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

Re: Select By Attributes

AllenDailey1 — Thu, 27 Jul 2023 18:34:43 GMT

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.html

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!

Re: Select By Attributes

JasonBagwell1 — Thu, 27 Jul 2023 21:08:31 GMT

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

Re: Select By Attributes

AllenDailey1 — Thu, 27 Jul 2023 21:14:22 GMT

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

Re: Select By Attributes

JohannesLindner — Thu, 27 Jul 2023 23:24:27 GMT

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:

Re: Select By Attributes

JasonBagwell1 — Fri, 28 Jul 2023 13:58:02 GMT

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

Re: Select By Attributes

JasonBagwell1 — Fri, 28 Jul 2023 14:02:41 GMT

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.

Re: Select By Attributes

JohannesLindner — Fri, 28 Jul 2023 23:37:55 GMT

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:

Re: Select By Attributes

JasonBagwell1 — Mon, 07 Aug 2023 15:29:06 GMT

Thanks Johannes,

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