A wildcard query cannot be performed in an attribute table

Description

In ArcMap, an 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:

Error:
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.

FIELD LIKE '42041%'

Wildcard symbols such as the percent sign and the asterisk can only be used in a string field.

Solution or Workaround

There are two options to perform a wildcard query on an attribute table.

• Using the CAST function
• Creating a new string field

If the first option cannot be carried out, apply the second.

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 Attributes 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 '323%' 

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.

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. Double-click the numeric field from the Fields list to populate the new field with values from the selected 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 '324%' 

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.