SQL Query not selecting all points that it should?

Question

I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).

What am I doing wrong?

My benzene column is a String.

Answer 1

Your problem is the data type as String.

You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).

If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...

As consequence you can't use operator like > or < with number set as string and expect a meaningful result.

The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)

Answer 2

When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).

You would need to change the datatype in your expression

int("Benzene") >= 7

You might have to use CInt() instead of int()