Select by Attributes based on SQL wildcard range

Question

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?

Answer 1

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.

Answer 2

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.