arcgis-desktop – How to Select Only Numbers in a String Field by Attribute Using SQL

arcgis-desktopselect-by-attributesql

Using a SQL experssion in the "Select by Attributes" table, how would I only select records 1, 4, 5, 8 and omit the rest (I only want to select values that are entirely numbers)? What I have right now is SUBSTRING(address,1,1)<='9' AND SUBSTRING(address,1,1)<> ' ', but it won't catch record 2(1234 S Complex).

Best Answer

Here is a suggestion:

CAST(address AS INTEGER) >= 0

Related Solutions

[GIS] Select all “NULL” values in the attribute table

Without knowing what your data looks like, the general syntax you need to insert into the select by attributes window is:

[FIELD_1] IS NULL OR [FIELD_2] IS NULL OR [FIELD_3] IS NULL

and so on for as many fields as you have to work with. For reference the full query would be:

SELECT * FROM [TABLE_NAME] WHERE [FIELD_1] IS NULL OR [FIELD_2] IS NULL OR [FIELD_3] IS NULL

[GIS] Select by Attribute on Date field using wildcards

If you date is stored as a date you can use this:

EXTRACT(HOUR FROM "Date_filed" ) = 6 
or
EXTRACT(HOUR FROM "Date_filed" ) = 7 
or
EXTRACT(HOUR FROM "Date_filed" ) = 8

IF your date is stored as a text field you could use this:

It's a little ugly, but you could use this

"Date_filed" LIKE '___________06:_____'
or
"Date_filed" LIKE '___________07:_____'
or
"Date_filed" LIKE '___________08:_____'

and just change the numbers to indicate the hours you are interested in.

or this should work:

"Date_filed" LIKE '% 06:%'
or
"Date_filed" LIKE '% 07:%'
or
"Date_filed" LIKE '% 08:%'

but as Martin suggested splitting the datetime up and adding a time field might be the best way to go.

Best Answer

Related Solutions

[GIS] Select all “NULL” values in the attribute table

[GIS] Select by Attribute on Date field using wildcards

Related Question