Using Wildcard in select by attributes

Question

I'm trying to use the % wildcard in select by attributes using SQL. It's a simple query. When I use it with a FIPS number it works but for some reason other fields with other numbers don't work. Every time I get ERROR 000358: Invalid expression.

Here is the SQL expression: VACANT LIKE '2%'

I'm just looking for the numbers that are in the 2000s.

bixb0012 · Answer

Creating a new field, or even casting to TEXT is unnecessary.  If the numbers are integers, you can rely on basic integer math to find all numbers within the 2000 range:

VACANT / 1000 = 2

DanRichert · Answer

Ah that makes sense. I didn't check and compare data types. I just created a new field and populated it with the VACANT field and then changed it to a text data type and then the wildcard search worked!

Thanks for getting back to me so quickly and for the helpful feedback!

AndyAnderson · Answer

FIPS numbers are text, and that’s the only type of field where '%' works. Do the other fields contain text representations of numbers or actual numbers? If the latter you need comparisons like VACANT >= 2000 AND VACANT < 3000.

— Andy