Doubt on "Select By Attributes" In ArcMap

326
8
10-30-2017 06:34 AM
PanGIS
by
Regular Contributor

Hi guys,

What is sounds very strange to me has probably a meaning for an expert in SQL or in the attribute selection in ArcGIS.

Why, if I search for all the records "greater or equal than" a letter, the letter is included in the search but if I search for "smaller or equal than" a letter, this is excluded?

It works the same either with or without %.

Thanks.

Tags (2)
0 Kudos
8 Replies
JoeBorgione
MVP Esteemed Contributor

Drop the % sign in both of your queries.  Instead  of value >= 'P%' just use >= 'P'  That will get you everything that starts with 'P' through Z.

If you use <= 'P', you'll get everything from A-P as well as those values that start with 0-9.

I use < 'A' all the time to find numerically 'named' streets...

can't wait to retire....
TedKowal
Regular Contributor II

It is because SQL compares strings by Ascii Values when using comparison operators.  If you insert a wildcard -- the representative strings returned are included. 

for example 

"p" > "P"   would return true  -- the decimal value for "p" is 112 and the decimal value for "P" is 80

name >= "P%" would compare name with the returned values of "Pxxxxxx"  So "P" by itself will be excluded, "Paulding" would be included as well as "p" (lower case has a higher ascii value than uppercase).

PanGIS
by
Regular Contributor

Thanks for your replies

Joe

even if I drop the % the result I get is exactly the same >= will include P and <= will exclude it.

A bit naive as question maybe, but if I type = my brain expects to get the "P" anyway, but my brain doesn't work in SQL.

Ted ,

you opened the door of a new world

Would you answer apply without considering the wildcard but the simple P?

(Maybe you already answer here above but I need you to confirm )

I checked the lowercase and it is here!! :

0 Kudos
JoeBorgione
MVP Esteemed Contributor

I just tried it with <= 'P%' on some street names; I got two streets named 'P' and no other streets that start with 'P'.  

I can get ALL the streets that start with 'P' using StreetName < 'Q' and of course all the others that go along with that.

To get all the streets that start with 'P' long with all streets less than 'P' I would use:

StreetName <= 'P%' or StreetName like 'P%'

What is your objective with these queries?

Pan gis

can't wait to retire....
0 Kudos
TedKowal
Regular Contributor II

Without the wildcard you would be comparing ascii value of P to ascii value of P so "in a sense" you are filtering name >= 80 or name <= 80. 

Please bear in mind a space also has a value....and in some database would influence the results:    P[space] <> P    "P " <> "P>. 

So technically my answer to you is yes....

However it can get complicated real quickly with other bumps in the road that I did not mention --- character sets that you are using will have some influence ... My assumption you are using the standard Latin-1 set.  Another bump in road is the database you are using.... some database use a dictionary for string comparison and others use a binary operation.  So in dictionary sense my example with space would not be correct  "P " = "P" whereas in a database using binary operations my answer would be correct  "P " <> "P".  I was trying real hard in keeping the answer simple.....  When you introduce "NULL" values there are even more issues!   

0 Kudos
TedKowal
Regular Contributor II

Personally I use coding when I am string comparing stuff using < > operators on letters..... then I can compare numbers!

Convert string to ASCII value python - Stack Overflow  

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I encourage you to read sql - Equals(=) vs. LIKE - Stack Overflow, I think it will help you understand your results.

ModyBuchbinder
Regular Contributor II

Try name <= 'Pz" 

This will find everything that start with a P (accept when it start with Pz) . I do not remember what is the next ascii after z

Have fun

Mody

0 Kudos