English

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

Description

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.

Cause

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

Code:
"FIELD" LIKE '42041%'


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

Solution or Workaround

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.
[O-Image]

2. Click the Select By Attribute tool
[O-Image]
in the attribute table to open the tool's dialog box.
[O-Image]

3. Type the script as highlighted below to select a particular set of field cells containing the digits 128.

Code:
CAST("Field_Name" AS VARCHAR(12)) LIKE '128%'

Note:
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.
[O-Image]

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.
[O-Image]

3. In the Add Field dialog box, set the field name, and select Text as the data type.
[O-Image]

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

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.

Code:
[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.

Code:
"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.
[O-Image]

Related Information