topic Re: SQL Statements using 'Like' in Data Management Questions

SQL Statements using 'Like'

JohnBrand — Thu, 06 Jan 2011 15:13:05 GMT

Is it possible to use the wildcard '%' on a numeric field (integer, double, etc) while using the 'like' operator?

example

I would like to select all numbers that end in zero. I know that

"ELEVATION" LIKE '%0'

works for strings. I would like to be able to use the expression on a numeric field.

Re: SQL Statements using 'Like'

NelsonDe_Miranda — Fri, 07 Jan 2011 19:08:56 GMT

To my knowledge there isn't a simple straight forward way as that to select values from a number field:

However, you can:

1) Convert your "numeric field to text" - as you mentioned above

OR

Use Mod Calculations and calculate a new field which will determine whether or not your number ends in a 0.

i.e.

MOD ( "Elevation" , 10) = 0
What this simple calc does is it selects all the values in the elevation that are divisible by 10.
i.e. numbers ending in 0.

Cheers,

Nelson

Re: SQL Statements using 'Like'

PaulHuffman — Thu, 27 Oct 2011 17:32:08 GMT

So why when I try MOD ( "intContour" , 1000) = 0 in a Select by Attributes query on a personal geodatabase feature class, I can only get "Syntax error (missing operator)" no matter where I place " or spaces or brackets"? It didn't seem to matter that I created "intContour" as an interger field from "Contour", a double field.

Re: SQL Statements using 'Like'

NelsonDe_Miranda — Thu, 27 Oct 2011 17:47:47 GMT

So using the same equation you just posted I was able to select contours that were divisible by 1000.

See screenshots below for the Query Box as well as the output result.

If you replicate what I did and still fail try flipping the equation ie. (this did not work for me but the original poster of the thread said it worked for him)

0 = [ELEVATION] MOD 1000
Source:http://lists.directionsmag.com/discussion/read.php?f=26&t=30698&a=1

Re: SQL Statements using 'Like'

PaulHuffman — Thu, 27 Oct 2011 18:07:21 GMT

Still didn't work. This is ArcGIS 10, sp3. Is there some different syntax MS Access requires because this is from a personal geodatabase feature class?

However, the flipped equation did work for me!

Re: SQL Statements using 'Like'

NelsonDe_Miranda — Thu, 27 Oct 2011 18:12:39 GMT

In Summary:

FGDB users use: MOD ( "Elevation" , 1000) = 0
Personal geodatabase users use: 0 = [ELEVATION] MOD 1000

The syntax difference is because MSAccess does not understand the MOD Operator in the same way the file geodatabase does.

Here is a link to the MSOffice help page showing the proper syntax for MOD within an Access environment.
http://office.microsoft.com/en-us/access-help/HV080756430.aspx

Re: SQL Statements using 'Like'

PaulHuffman — Thu, 27 Oct 2011 20:00:09 GMT

Thanks - Nelson. When I typed MOD into MS Access Help Search I got pages of unrelated crap.

[intContour] MOD 1000 = 0 worked for me as well. Didn't matter in I used "Contour", the double version of my item or "intContour" the short integer.