topic Re: Filter Issue with Underlying Query CHAR_LENGTH in ArcGIS Experience Builder Questions

Filter Issue with Underlying Query CHAR_LENGTH

RyanCoodey — Tue, 03 Oct 2023 23:06:03 GMT

Hi, we are running an 11.1 Portal and no issues with the built in ExB 1.10. But using the latest 1.12 external from the Portal with an app registration and having troubles.

The Filter widget is not loading the unique value list and seeing a handful of errors in the console. The main one being it cannot execute a query against the AGS endpoint.

Example Endpoint: https://x.domain.com/arcgis/rest/services/Global/Task/MapServer/0/query

Example WHERE: (FIELD_NAME IS NOT NULL AND CHAR_LENGTH(FIELD_NAME) > 0)

With some trial and error testing, see the issue is with the CHAR_LENGTH function. The error in Server Manager is: "'CHAR_LENGTH' is not a recognized built-in function name.]". The underlying geodatabase is on SQL Server 2016.

Any way to bypass this CHAR_LENGTH() check? Or have it use LEN() instead? The seems to be a bug depending on the underlying database that was introduced between 1.10 and 1.12.

Thanks for any help!

Re: Filter Issue with Underlying Query CHAR_LENGTH

JeffreyThompson2 — Wed, 04 Oct 2023 12:34:04 GMT

I have seen an issue where the filter widget will not load values after a change to the database schema. I think I cleared the error by deleting and re-adding the filter.

Re: Filter Issue with Underlying Query CHAR_LENGTH

RyanCoodey — Wed, 04 Oct 2023 15:01:29 GMT

Thanks for the tip @JeffreyThompson2 , I tried re-adding the filter, tried a brand new ExB app, and tried connecting to a completely different geodatabase (Oracle instead of SQL Server). They all unfortunately error on the filter query. Seemingly because they try to use the CHAR_LENGTH() function. Only option that works for me currently is with the Portal built in 1.10 ExB filter.

Thanks again though for the idea to try!

Re: Filter Issue with Underlying Query CHAR_LENGTH

RyanCoodey — Wed, 04 Oct 2023 21:51:02 GMT

I am no ExB expert and also new to the standalone version but found a fix. In the file "\ArcGISExperienceBuilder\client\dist\jimu-core\index.js", search for "CHAR_LENGTH" and replace with "LEN".

Not sure what the ramifications are for this change when deploying, but for the Builder and Published Item it seems to work. It likely will break when using other flavors of databases though. Hopefully this gets a proper fix in an update.

Re: Filter Issue with Underlying Query CHAR_LENGTH

TonghuiMing — Fri, 27 Oct 2023 08:12:36 GMT

@RyanCoodey Glad that you've found a workaround, and we will log this in for the details. Thanks for catching this.

Re: Filter Issue with Underlying Query CHAR_LENGTH

GiuseppeBaiamonte — Wed, 13 Nov 2024 04:52:06 GMT

We are using ArcGIS Online. We have the same issue with Experience Builder query and filter widgets. The services are provided by a SQL server 2017 instance.

We are unable to implement the suggested solution and have not found any workarounds.

Will this issue be fixed?

@TonghuiMing any suggestions? Should I contact Esri Technical Support?

Re: Filter Issue with Underlying Query CHAR_LENGTH

DanielGarcia — Tue, 17 Feb 2026 16:24:29 GMT

Current Experience Builder in ArcGIS Online (February 2026) has the same issue. CHAR_LENGTH its only valid in service that use ArcGIS SQL syntax. According to the following article, if all layers in the service come from the same datasource location and this location is a Enterprise Database, the service use RBDMS Sintax for layer queries.

SQL reference for query expressions used in ArcGIS

Other possibility is that ArcGIS Server has been configured to avoid standardized queries

About standardized queries

Use a Customize version of ExB is not a valid solution for this issue. Exb should be able to deal with this configuration.

Why haven't they resolved this issue yet?