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; } } }

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

Najděte více příspěvků označených jako

Geodatabase

Accepted answers

JohannesLindner

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:

All comments

JasonBagwell1

Thanks Johannes,

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

JohannesLindner

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

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.

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:

AllenDailey1

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

JasonBagwell1

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

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

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