SQL syntax for numeric wildcard in file geodatabase

3661
10
Jump to solution
04-21-2016 11:07 AM
FrankPrice
New Contributor III

Does anyone know what the appropriate SQL syntax is for selecting any record that contains any number within a text string in a field using Select by Attributes with a file geodatabase feature class.  The help page mentions using the pound sign as a numeric wildcard but this isn't working for me so maybe it only works for personal geodatabases?  And I know I can use the field calculator and python or VB to do this in various ways but it just seems like there should be a quicker way to do it with SQL using Select by Attributes.  I'm thinking there should be something like Field_name LIKE '%#%' where the pound sign is any number.

Thanks for any pointers!

0 Kudos
1 Solution

Accepted Solutions
WesMiller
Regular Contributor III

Obs_data_temp LIKE '%0%' OR

Obs_data_temp LIKE '%1%' OR

Obs_data_temp LIKE '%2%' OR

Obs_data_temp LIKE '%3%' OR

Obs_data_temp LIKE '%4%' OR

Obs_data_temp LIKE '%5%' OR

Obs_data_temp LIKE '%6%' OR

Obs_data_temp LIKE '%7%' OR

Obs_data_temp LIKE '%8%' OR

Obs_data_temp LIKE '%9%'

View solution in original post

0 Kudos
10 Replies
WesMiller
Regular Contributor III

If your data is stored in an sql database try this PATINDEX('%[0-9]%', yourfieldhere ) > 0

0 Kudos
FrankPrice
New Contributor III

Thanks, but I just get "An invalid SQL statement was used".  Is the PATINDEX function specific to SQL Server?  I need something that will work on a file geodatabase...

0 Kudos
WesMiller
Regular Contributor III

Try this for a File geodatabase

yourfieldhere LIKE '%[0-9]%'

0 Kudos
FrankPrice
New Contributor III

I tried that previously and again when you suggested.  It doesn't throw an error but doesn't select anything either (yes, there are definitely numbers in the field I'm querying).

0 Kudos
WesMiller
Regular Contributor III

Are you in a file geodatabase?

Could you post a sample 5-10 records?

0 Kudos
FrankPrice
New Contributor III

Check out the attached.  I deleted most of the fields but tested and still have the same problem.  Try the Obs_data_temp field.

0 Kudos
WesMiller
Regular Contributor III

Obs_data_temp LIKE '%0%' OR

Obs_data_temp LIKE '%1%' OR

Obs_data_temp LIKE '%2%' OR

Obs_data_temp LIKE '%3%' OR

Obs_data_temp LIKE '%4%' OR

Obs_data_temp LIKE '%5%' OR

Obs_data_temp LIKE '%6%' OR

Obs_data_temp LIKE '%7%' OR

Obs_data_temp LIKE '%8%' OR

Obs_data_temp LIKE '%9%'

0 Kudos
FrankPrice
New Contributor III

Well, not very concise but I guess it does the job...

0 Kudos
WesMiller
Regular Contributor III

I've looked a few times for a good answer to this question, unfortunately unless your in server or a personal geodatabase there doesn't seem to be a good answer.

0 Kudos