Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I want to select all the records from a geodatabase table where the string starts with a letter, so I tried

SELECT *
FROM tbl_names
WHERE "name" LIKE '[A-Z]%'

This returned no records. After some searching, I found that this is SQL Server syntax. I didn't think this would be a problem though, as far as I know all versions of SQL support %. After going through the help file on building a query expression, I saw that the correct syntax is

SELECT *
FROM tbl_names
WHERE "name" >= 'A'

As the names are all strings, my first instinct was to try LIKE. Why is the >= operator used instead of LIKE? Are wildcard ranges not supported in ArcMap?

share|improve this question
    
I don't know this database, but which 'side' does it sort digits on? That is, does 0 come after Z (the usual, I think), or before A? A number of other DBMSs don't support this sort of functionality, so you have to use knowledge of slightly different things. Also, what about lower-case characters (or is everything upper case)? And what about non-English characters (not A-Z)? –  Clockwork-Muse Nov 14 '12 at 17:31
    
When sorting a field in ascending order, strings appear as follows: !z,?,0,0a,1,10,2,ant,A,Ant,z,Z. So special characters, digits, (case-sensitive) letters. –  Arabella Nov 14 '12 at 19:51
add comment

2 Answers

Wildcards in general are supported by ArcMap. Here is an extract from the help you get while you do a 'Select by Attributes':


Use the LIKE operator (instead of the = operator) to build a partial string search. For example, this expression would select Mississippi and Missouri among the USA state names:

"STATE_NAME" LIKE 'Miss%'

You can use greater than (>), less than (<), greater than or equal (>=), less than or equal (<=) and BETWEEN operators to select string values based on sorting order. For example, this expression will select all the cities in a coverage with names starting with the letters M to Z:

"CITY_NAME" >= 'M' The not equal (<>) operator can also be used when querying strings.

Wildcard Characters A wildcard character is a special symbol that stands for one or more characters.

For any file-based data, '%' means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use '_'.

For example, this expression would select any name starting with the letters Cath, such as Cathy, Catherine, and Catherine Smith:

"NAME" LIKE 'Cath%'

But this expression would find Catherine Smith and Katherine Smith:

"OWNER_NAME" LIKE '_atherine smith' The wildcards you use to query personal geodatabases are '*' for any number of characters and '?' for one character.

Wildcard characters appear as buttons on the query dialog. You can click the button to enter the wildcard into the expression you’re building. Only the wildcard characters that are appropriate to the data source of the layer or table you are querying are displayed.

If you use a wildcard character in a string with the = operator, the character is treated as part of the string, not as a wildcard.

With a joined table, use wildcards appropriate for the side of the join that you are querying. If the query only applies to fields in the target table (the left-side table), use the target table wildcards. If the query only applies to fields in the join table (the right-side table), use the join table wildcards. If the query involves fields from both sides of the join, use the '%' and '_' wildcards.

For example, if you join a dbf file (the join table) to a personal GDB feature class (the target table):

  1. Use * for queries that only involve personal GDB fields.

  2. Use % for queries that only involve dbf columns.

  3. Use % for queries involving columns from both sides of the table.


According to this: I think ranges are not supported, instead you have to use > and <, just the way you did it.

share|improve this answer
    
I understand that wild cards can be used, as the method I was using most of the time to select substrings was WHERE "name" LIKE '%substring%'. It was only when I needed to look for a string in a specific format, like a regex in the form [0-9][0-9][A-Z]% that I realised that it wouldn't accept wildcard ranges. –  Arabella Nov 14 '12 at 19:42
add comment

Yes you can use wildcards in LIKE statements. I've never used ranges via ArcMap but you're using the correct syntax from a SQL Server point of view.

A quick word of warning if you're going to use the '>=' operator though. The outcome of this will differ depending on what collation is set. This can alter how sorting is performed on the data, e.g. whether it's case sensitive or not. So in some cases you may find your query returns only values starting with upper case letters, and sometimes both upper and lowercase.

See http://sqlblog.com/blogs/louis_davidson/archive/2007/05/20/sorting-and-case-sensitive-collations.aspx.

Also, if you're just interested in the first character of the field then you could use

WHERE SUBSTRING("name", 1, 1) >= 'A'

rather than

WHERE "name" >= 'A'

This might have performance benefits if nothing else.

share|improve this answer
    
Actually, no, the SUBSTRING method is unlikely to increase performance, as it (usually) means that any indices on name would be ignored. Yes, you may have a higher cost for comparisons (given character lengths), but most implementations I'm aware of would return after the first character is compared anyways... I personally doubt any optimizer is written to realize SUBSTRING(column, 1, 1) is returning just the start of the string –  Clockwork-Muse Nov 14 '12 at 17:26
    
Would I be able to work around the case-sensitivity by using upper? I'm not concerned about case for this exercise though, but it would be good to keep in mind. –  Arabella Nov 14 '12 at 19:45
    
Yes, you're absolutely right. There's unlikely to be a performance benefit to using SUBSTRING in this case, and could have the opposite effect. –  pecoanddeco Nov 15 '12 at 13:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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