Remove leading and trailing spaces from strings using SQL in ArcGIS Online and Portal for ArcGIS

Summary

ArcGIS Arcade is disabled in the Calculate Field dialog box when synchronization is enabled on a hosted feature layer. In some cases, the hosted feature layer attribute table has data containing leading and trailing spaces in the field values. The spaces in the field values can be removed using SQL expressions.

This article provides instructions to remove the leading and trailing spaces from strings using SQL expresions in ArcGIS Online and Portal for ArcGIS.

The image below shows the hosted feature layer attribute table with leading and trailing spaces in the field values in ArcGIS Online.

Procedure

1. Open the hosted feature layer’s item details page, and click the Data tab.
2. Select the layer to be edited from the Layer drop-down menu if there are multiple layers in the hosted feature layer.
3. Click the target field column, and click Calculate. In this example, the target field is District Name.

4. In the Calculate Field dialog box, click SQL.
5. In the Expression dialog box, insert the appropriate SQL expression, as described below.

TRIM(BOTH ' ' FROM <fieldName>)

TRIM(BOTH ' ' FROM District)

Note:
If the field values has only leading spaces, change the BOTH syntax to LEADING. If the field values has only trailing spaces, change the BOTH syntax to TRAILING. Refer to ArcGIS Online: Which SQL functions are supported in ArcGIS Online? for more information.

6. Click the Validate button to ensure there are no errors in the expression. When the expression is complete and valid, click Calculate.

The image below shows the leading and trailing spaces are removed from the field values.