topic Re: SQL syntax for numeric wildcard in file geodatabase in Data Management Questions

SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 18:07:55 GMT

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!

Re: SQL syntax for numeric wildcard in file geodatabase

WesMiller — Thu, 21 Apr 2016 18:19:32 GMT

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

Re: SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 18:33:42 GMT

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...

Re: SQL syntax for numeric wildcard in file geodatabase

WesMiller — Thu, 21 Apr 2016 18:54:01 GMT

Try this for a File geodatabase

yourfieldhere LIKE '%[0-9]%'

Re: SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 19:10:32 GMT

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).

Re: SQL syntax for numeric wildcard in file geodatabase

WesMiller — Thu, 21 Apr 2016 19:22:20 GMT

Are you in a file geodatabase?

Could you post a sample 5-10 records?

Re: SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 19:33:23 GMT

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

Re: SQL syntax for numeric wildcard in file geodatabase

WesMiller — Thu, 21 Apr 2016 19:43:11 GMT

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%'

Re: SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 19:49:13 GMT

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

Re: SQL syntax for numeric wildcard in file geodatabase

WesMiller — Thu, 21 Apr 2016 19:54:44 GMT

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.

Re: SQL syntax for numeric wildcard in file geodatabase

FrankPrice — Thu, 21 Apr 2016 20:10:03 GMT

Thanks for your help with it!