9

Using ArcGIS 10.2.2, I have a string field in an SDE attribute table. I need help creating a query to select all records that have a zero in the 6th and 7th character of the field (i.e 1995-0023A). To help me better understand this type of query, I would also like to be able to see a query that selects records with a zero in just the 6th character of the field. I can then use the "and" clause and duplicate it to select the 7th character as well.

I've tried variations of "FIELD1" LIKE '%00_______' but it doesn't select records with the double zeroes in the correct place in the string.

  • I've tried variations of "FIELD1" LIKE '%00_______' but it doesn't select records with the double zeroes in the correct place in the string. – Steve May 24 '17 at 17:44
  • Something like "FIELD1" LIKE '_____00%' ? That would use five character wildcards, then your two zeros, then another wildcard for the rest of the string. What type of database are these in? SQL Server, Oracle, something else? Please edit your question to include the info from your comment above, and details about your database (version release etc.) – Midavalo May 24 '17 at 17:48
  • "field_name" LIKE '%00%' this will return all fields with double zeros next to each other doesnt use the 6th or 7th position if that helps. – NULL.Dude May 24 '17 at 17:48
  • An underscore _ is used in some databases as a single character place wildcard. A percent % is wildcard for all character places. – Midavalo May 24 '17 at 17:50
  • 1
    Thanks guys, looks like I had the the query reversed. Midavalo's suggestion worked: FIELD1 LIKE '_____00%'. Its an Oracle DB. – Steve May 24 '17 at 17:53
12

An underscore _ is used in some databases as a single character place wildcard. A percent % is wildcard for all character places.

Therefore, something like "FIELD1" LIKE '_____00%' should work, depending on the type of database. That would use five character wildcards, then your two zeros, then another wildcard for the rest of the string.

  • would this work in MS SQL Server DB as well? – NULL.Dude May 24 '17 at 19:05
  • 1
    @Joe Yes it should - I have used this method for similar queries in the past in SQL Server, but I cannot test this right now. – Midavalo May 24 '17 at 19:13
3

The following would select all records that have a zero in the 6th character position as well as in the 7th character position.

FIELDNAME like '_____0%' and FIELDNAME like '______0%'

There are five underscores preceding the zero in the first part of the expression and then six underscores preceding the zero in the second part. The underscore is a 'wildcard' that requires any character to be present (can be a space, a number, a letter, a symbol, etc.). The percent (%) wildcard allows anything to follow (also allows nothing to follow - does not require any character).

A more succinct way to query this would be

FIELDNAME like '_____00%'
-1

This is RDBMS dependent, though the following should work in most:

SUBSTRING(fieldname, 6, 2) = '00'

  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. Have you had this work successfully? In my experience the SUBSTRING() never worked in ArcMap's limited SQL. Is this where you use it, or do you use this elsewhere? Could you edit your answer to expand on this? – Midavalo May 25 '17 at 13:32
  • The OP stated this was in SDE, not a file geodatabase, mdb, or shapefile. SDE dictates an RDBMS. The ArcMap query is based upon the where portion of the SQL, based on the particular RDBMS where the SDE exists (important to know what type of database the data is in, as syntax can differ). Yes, I've had this perform successfully numerous times in my 20+ years as an SDE Administrator. I've thrown this in as an option, as not all RDBMS's will utilize the '_' option. My solution is simplistic and arguably old, but it works in an SDE environment. – CaptRay May 25 '17 at 14:21
  • I asked as I had tested this last week and it didn't work. I may have done it wrong, but it didn't work for me. – Midavalo May 25 '17 at 14:23
  • In SDE? If so, which RDBMS does your SDE sit on? Truly, I am curious, as the RDBMS environments intrigue me, particularly for differences. I tested it (successful on Oracle and SQL Server) before posting as well. SUBSTRING will be invalid for shapefiles. It will work for file geodatabases. – CaptRay May 25 '17 at 14:34
  • btw, Oracle's syntax is SUBSTR. – CaptRay May 25 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.