Using Wildcard in select by attributes

05-11-2022 06:41 PM
New Contributor

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. 


3 Replies
Occasional Contributor II

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

New Contributor

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!

0 Kudos
MVP Esteemed Contributor

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
0 Kudos