Problem:  A wildcard query cannot be performed in an attribute table

相关信息
Article ID: 44324
Bug Id: N/A
Software:
ArcGIS for Desktop Advanced 10.1, 10.2, 10.2.1, 10.2.2, 10.3
ArcGIS for Desktop Standard 10.1, 10.2, 10.2.1, 10.2.2, 10.3
ArcGIS for Desktop Basic 10.1, 10.2, 10.2.1, 10.2.2, 10.3
Platforms: N/A

问题描述
In ArcMap, a SQL query performed on an attribute table to select a particular set of digits from a field is not possible.

A
wildcard query is used to substitute any other character in a string. When a wildcard query is carried out using an attribute query, the following error is returned.

"An invalid SQL statement was used."

The error is returned during the verification of the expression.

原因
This issue occurs when a wildcard query is carried out on a numerical field using an expression such as the one displayed below.


"FIELD" LIKE '42041%'

Wildcard symbols such as '%' and '*' can only be used in a string field.
已邀请:

EsriSupport

赞同来自:

解决方案
There are two options to perform a wildcard query on an attribute table.

If Option A cannot be carried out, apply Option B.


Option A - Use the CAST function in the attribute table. In this example, the CAST() function converts the field to a string of characters to allow the wildcard query on the number field. 1. Right-click the selected layer in the Table Of Contents, and select Open Attribute Table.


2. Click the Select By Attribute tool


in the attribute table to open the tool's dialog box.

3. Type the script as highlighted below to select a particular set of field cells containing the digits 128.
CAST("Field_Name" AS VARCHAR(12)) LIKE '128%'

VARCHAR denotes the character length and must be specified when using the CAST function.

4. Click the Verify button to verify the expression.
5. Click the Apply button, and the particular set of digits is selected.


Option B - Create a new string field, and compute the old field to the new field. 1. Right-click the selected layer in the Table Of Contents, and select Open Attribute Table. 2. Click the Table Options drop-down arrow, and select Add Field.


3. In the Add Field dialog box, set the field name, and select Text as the data type.



4. Click OK.
5. Right-click the newly created field, and select Field Calculator.


6. If a warning on calculating outside of an edit session is displayed, click Yes.
7. Type the script as highlighted below in the Field Calculator.


[New_Text_Field]=[Old_Numeric_Field]

8. Click the Select By Attribute tool in the attribute table to open the tool's dialog box.
9. Type the following expression to return field cells with digits of 127.


"New_Text_field" LIKE "127%"

10. Click the Verify button to verify the expression.
11. Click the Apply button, and a particular set of digits in a field is selected.




其它相关参考
  1. ArcGIS Help: Building a query expression
  2. ArcGIS Help: SQL reference for query expressions used in ArcGIS
  3. ArcGIS Help: Using Select By Attributes


创建及修改时间
Created: 3/23/2015 Last Modified: 4/19/2015
原文链接
http://support.esri.com/en/kno ... 44324

要回复问题请先登录注册