Select to view content in your preferred language

Select By Attributes

1429
8
Jump to solution
07-27-2023 10:52 AM
Labels (1)
JasonBagwell1
New Contributor III

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

1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_0-1690587306095.png

 

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

JohannesLindner_1-1690587455493.png

 

 


Have a great day!
Johannes

View solution in original post

8 Replies
AllenDailey1
Occasional Contributor III

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:

AllenDailey1_0-1690482826794.png

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

I hope that helps!

0 Kudos
JasonBagwell1
New Contributor III

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

JasonBagwell1_0-1690492073845.png

 

0 Kudos
AllenDailey1
Occasional Contributor III

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

JasonBagwell1
New Contributor III

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

JasonBagwell1_0-1690552655449.png

 

0 Kudos
JohannesLindner
MVP Frequent Contributor

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:

JohannesLindner_0-1690500253382.png

 


Have a great day!
Johannes
JasonBagwell1
New Contributor III

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_1-1690552944123.png

 

0 Kudos
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_0-1690587306095.png

 

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

JohannesLindner_1-1690587455493.png

 

 


Have a great day!
Johannes
JasonBagwell1
New Contributor III

Thanks Johannes,

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

0 Kudos