NAME: Case-Sensitive Queries, v. 1.4 (Click Name to Download)
Aka: case_sensitive.avx
Last modified: January 2, 2005
TOPICS: Case Sensitive Lowercase Uppercase Capital Letter Text String
AUTHOR:
Jeff Jenness
GIS Analysis and Application Design
Jenness Enterprises
3020 N. Schevene Blvd.
Flagstaff, AZ 86003 USA
jeffj@jennessent.com
(928) 607-4638
DESCRIPTION: This is a simple extension that provides functions for case-sensitive queries on tables and feature themes. If it is important for you to distinguish between, for example, “III” and “IIi”, then the normal ArcView Query function will not work unless you enable the Case Sensitive mode using Avenue. By default, ArcView queries are not case-sensitive and do not consider whether words have uppercase or lowercase letters.
This extension offers a toggle menu item in both the View “Theme” menu and the Table “Table” menu which allows you to turn the case-sensitive mode on and off. Queries using the standard query dialog will be either case-sensitive or not, depending on the case-sensitive mode.
This extension also offers a separate query dialog which will always conduct a case-sensitive query on a string field, regardless of whether the case-sensitive mode is turned on or off.
IMPORTANT: Because of a known bug in ArcView, standard case-sensitive string operations may not work under Windows NT (although they do seem to work with ArcView 3.3 under Windows 2000 and XP). Therefore this extension includes a separate function to conduct basic case-sensitive queries under Windows NT. The algorithms used in this separate method are described at the end of this document.
Also (with thanks to Farley Klotz), Joins on tables ARE case-sensitive, but Links on tables are NOT case-sensitive. This extension therefore offers a work-around to allow you to convert fields to ASCII versions, which can then be used to force a case-sensitive link.
Quick Links
General Instructions |
Toggling Case-Sensitive Mode
|
Case-Sensitive Queries
Case-Sensitive
Queries (Win NT) |
Case-Sensitive Joins and Links
REQUIRES: This extension requires that the file "avdlog.dll" be present in the ArcView/BIN32 directory (or $AVBIN/avdlog.dll) and that the Dialog Designer extension be located in your ArcView/ext32 directory, which they usually are if you're running AV 3.1 or better. The Dialog Designer doesn't have to be loaded; it just has to be available. If you are running AV 3.0a, you can download the appropriate files for free from ESRI at:
http://support.esri.com/index.cfm?fa=downloads.patchesServicePacks.viewPatch&PID=25&MetaID=483
REVISIONS: None Yet
Recommended Citation Format: For those who wish to cite this extension, the author recommends something similar to:
Jenness, J. 2005. Case sensitive query (case_sensitive.avx) extension for ArcView 3.x. Jenness Enterprises. Available at: http://www.jennessent.com/arcview/case_sensitive.htm.
Please let me know if you cite this extension in a publication ( jeffj@jennessent.com ). I will update the citation list to include any publications that I am told about.
-
Put the extension file “case_sensitive.avx” file into the ArcView extensions directory (../../Av_gis30/Arcview/ext32/).
-
After starting ArcView, load the extension by clicking on File and then Extensions… , scrolling down through the list of available extensions, and then clicking on the checkbox next to the extension called “Case Sensitive Query”
-
The extension will add new menu item labeled “Case Sensitive Query” and “Turn ON/OFF case-sensitive mode” to both your Table “Table” menu and your View “Theme” menu:
Toggling the Case-Sensitive Mode (Not for Windows NT Platform! See below):
You can set ArcView to consider all search strings as Case-Sensitive by turning on the case-sensitive mode. Click the “Turn ON/OFF case-sensitive mode:” menu item to switch the mode on and off. If case-sensitive mode is turned on, all queries using your standard query dialog will treat the search strings as case-sensitive. For example, consider a sample point dataset with an attribute table named “String”, with each record containing one of the following values:
III, IIi, IiI, iII, Iii, iIi, iiI, iii
These are just all possible upper- and lowercase permutations of the string “iii” .
Normally, doing a query for the string “iii” would select all points because the query is not case-sensitive:
If case-sensitive mode is turned on, only those features with attribute values exactly equal to “iii” will be selected:
As with other standard ArcView queries, you can search for multiple values, within multiple fields, and using various logical operators (=, >, <, <>, >=, <=, and, or, not). If you use > or < operators in case-sensitive queries, keep in mind that uppercase letters come before lowercase letters. For example, the list:
"III", "IIi", "IiI", "iII", "iiI", "iii", "iIi", "Iii"
would be sorted, in ascending order, as:
"III", "IIi", "IiI", "Iii", "iII", "iIi", "iiI", "iii"
Case-Sensitive Query Menu Item (Not for Windows NT Platform! See below):
-
This “Case-Sensitive Query” menu item will only be enabled in your View menu if you have a single feature theme active in your view, and will operate directly on that theme.
-
Clicking this menu item will open the “Case-Sensitive Search:” dialog:
-
Select the field you would like to query. NOTE: This function only allows you to query one field at a time.
-
Decide whether you want to search for the exact query string, or just for those field values that contain the query string. For example, if you wanted to select the record containing the word “Sample”, then the Exact Query String option would require you to enter the complete word “Sample”. The Contains Query String option would select this record if you entered “Sam” or “amp”.
-
Enter your query string. Do not use quotation marks unless the string you are looking for actually has quotation marks.
-
Click the selection style button you want to use:
 |
New Set: Generates a brand new selection set, clearing any selection which might have existed previously. |
|
Add To Set: Maintains any previous selection set, and adds any records that meet your search string. |
|
Select From Set: Only selects records which meet your search string AND were previously selected. |
|
Subtract From Set: Only considers records which were previously selected, and deselects any records which meet your search string. |
|
Either, Not Both: Records will be selected only if EITHER they were previously selected, OR if they meet the query string, but not both. |
This dialog will stay open after you execute your query, allowing you to easily enter other queries and other selection styles. Click “Close” or the “X” at the top right corner to close the dialog.
Case-Sensitive Query (Windows NT) Menu Item:
The “Toggle Case-Sensitive Mode” and “Case-Sensitive Query Dialog” above both rely on the Avenue request “String.SetCaseSensitiveCompare()”, which is supposed to tell ArcView whether string operations are case-sensitive or not. However, there is a known bug with this request when running ArcView on Windows NT. According to ESRI Germany and the Clearquest Database (with thanks to Fridjof Schmidt, who forwarded this to me, and to the anonymous person who initially reported this problem), a description of this bug has been recorded as follows:
SetCaseSensitiveCompare should work with other string-based requests other than = (equal)
Defect ID CQ00090042
Call Tracking ID 50454
Project avenue
Command Name SetCaseSensitiveCompare
Client Platform ALL_NT
Client OS NT 4.0 sp3
Description I tried to use the request String.SetCaseSensitiveCompare(TRUE) to use string requests in a case sensitive way. It turns out that that doesn't work. The only request (as far as I know) that does anything with this setting is the "=" request. If you compare two strings using "ABC" = "abc", they will return false if SetCaseSensitiveCompare is set to true. But every other string-based request will ignore the SetCaseSensitiveCompare setting. For instance IndexOf, Contains and Substitute work the same regardless of the SetCaseSensitiveCompare setting. This is clearly designed to work that way, but it is also very frustrating for users to find out that a thing like this doesn't work, even though it logically should. I would like ESRI to change the behavior of the requests IndexOf, Contains, Substitute and any other relevant request I forgot to take into account the contents of the SetCaseSensitiveCompare setting.
Therefore the “Toggle Case-Sensitive Mode” and “Case-Sensitive Query Dialog” functions above may not work under Windows NT. Oddly, they do seem to work under Windows 2000 and Windows XP. Based on tests by Fridjof and myself, the “SetCaseSensitiveCompare” request:
 |
FAILS with ArcView 3.2a under Windows NT: Fridjof Schmidt reports that the “SetCaseSensitiveCompare” request definitely does NOT work when using ArcView 3.2a on Windows NT. |
|
SUCCESSFUL with ArcView 3.3 on Windows 2000 |
|
SUCCESSFUL with ArcView 3.3 on Windows XP |
|
SUCCESSFUL with ArcView 3.2 on Windows 95 |
THIS EXTENSION OFFERS A WORKAROUND for those who have trouble using the “Toggle Case-Sensitive Mode” and “Case-Sensitive Query Dialog” functions above. Unfortunately I have not thought of a way to force ArcView to do the full range of case-sensitive string comparisons, but I have provided a tool which will query a table for an exact string. This function is identical in appearance and function to the “Case-Sensitive Query” dialog above. If you are interested, the methods used are described below in Step 7.
-
This “Case-Sensitive Query” menu item will only be enabled in your View menu if you have a single feature theme active in your view, and will operate directly on that theme.
-
Clicking this menu item will open the “Case-Sensitive Search:” dialog:
-
Select the field you would like to query. NOTE: This function only allows you to query one field at a time.
-
Decide whether you want to search for the exact query string, or just for those field values that contain the query string. For example, if you wanted to select the record containing the word “Sample”, then the Exact Query String option would require you to enter the complete word “Sample”. The Contains Query String option would select this record if you entered “Sam” or “amp”.
-
Enter your query string. Do not use quotation marks unless the string you are looking for actually has quotation marks.
-
Click the selection style button you want to use:
|
New Set: Generates a brand new selection set, clearing any selection which might have existed previously. |
|
Add To Set: Maintains any previous selection set, and adds any records that meet your search string. |
|
Select From Set: Only selects records which meet your search string AND were previously selected. |
|
Subtract From Set: Only considers records which were previously selected, and deselects any records which meet your search string. |
|
Either, Not Both: Records will be selected only if EITHER they were previously selected, OR if they meet the query string, but not both. |
This dialog will stay open after you execute your query, allowing you to easily enter other queries and other selection styles. Click “Close” or the “X” at the top right corner to close the dialog.
If you are curious as to the exact ASCII value of any particular letter, copy the text below into a blank script, compile and run it:
| ' START OF SCRIPT ------------------------------------------------ |
| theReport = "" |
| for each anIndex in 0..255 |
| theReport = theReport+anIndex.AsString+" = "+anIndex.AsChar+NL |
| end |
| msgBox.Report(theReport, "") |
| ' END OF SCRIPT -------------------------------------------------- |
Run the following code for a sample of the actual query string that the tool generates:
| ' START OF SCRIPT ------------------------------------------------ |
| theString = msgBox.Input("Enter Case-Sensitive Search String:", |
| "Case Sensitive Search:", "") |
| if (theString = nil) then return nil end |
| theQueryString = "([theFieldName].Count = "+theString.Count.AsString+") AND " |
| for each aCharIndex in 0..(theString.Count-1) |
| theQueryString = theQueryString +"([theFieldName"+ |
| "].Middle("+aCharIndex.AsString+",1).AsAscii="+ |
| theString.Middle(aCharIndex, 1).AsAscii.AsString+") AND " |
| end |
| theQueryString = theQueryString.basicTrim("", " AND ") |
| msgBox.Report(theQueryString, "") |
| ' END OF SCRIPT -------------------------------------------------- |
Case-Sensitive Joins and Links:
When joining tables, ArcView does use a case-sensitive comparison. The value “Arizona” will not be joined with the value “arizona”. Unfortunately ArcView does not consider the upper- or lowercase status of strings when linking tables, even when the Case Sensitive mode is turned on. This extension offers a work-around method that is somewhat awkward, but it will allow you to link tables based on case-sensitive comparisons.
It works by creating a new field containing ASCII versions of the original values. For example, if the lowercase character “i” has an ASCII value of 105, and the uppercase character “I” has an ASCII value of 73, then the string “iiI” would be converted to “105_105_73” and the string “II” would be converted to “73_73”.
You need to create new ASCII fields in all the tables you plan to link, and then link them based on the ASCII fields instead of the original fields.
Do this by clicking the “Convert Field to ASCII” menu item in either the Table “Table” menu or the View “Theme” menu. You will first need to identify your table and field. If you are working with a Table document, then the table will be the active document and the field will be the active field. If there is no active field, or if the field is a Shape field, then you will be prompted to identify the field to convert.
If you are working in a View document, then you will be prompted to identify your table and field:
You will then be asked what to name your new field:
Click “OK” and the extension will go to work. If any of your records are selected, then it will only convert those selected records. Otherwise it will convert all records. When it finishes, you will have a new field containing ASCII versions of the input field values:
IMPORTANT: String fields in ArcView tables can be no more than 254 characters long. The conversion to ASCII adds a lot of characters to the original value (for example, converting “iii” to “105_105_105” increases the number of characters from 3 to 11), so you need to be sure that your ASCII version is less than 254 characters long. The worst-case scenario would be that the ASCII version would have 4 times as many characters as the original. Do the following to check the maximum length of your string field:
1. Set your table to “Editable” and add a new numeric field.
2. Click on your new field, click the “Calculate” button, and enter the following string (substituting “the_field_name” with the name of the field of interest):
[the_field_name].Count
3. Sort your new field and find the highest value.
4. Click the “Stop Editing” menu item. You may decide whether to save the edits or not.
Enjoy! Please contact the author if you have problems or find bugs.
Jeff Jenness
jeffj@jennessent.com
Jenness Enterprises
http://www.jennessent.com
3020 N. Schevene Blvd.
(928) 607-4638
Flagstaff, AZ 86004 USA
Please visit Jenness Enterprises ArcView Extensions site for more ArcView Extensions and other software by the author. We also offer customized ArcView-based GIS consultation services to help you meet your specific data analysis and application development needs.
