Attribute queries using Unicode strings in SQL Server do not return correct results

Description

Attribute queries that include a Unicode string do not produce the correct result against an enterprise geodatabase on SQL Server or a SQL Server database.

In the following example, using the query builder in ArcGIS Pro, the attribute column being queried contains country names in several different languages and alphabets, in this example, Japanese.

Country_local = '日本'

For the example above, no results are returned unless the SQL Server database being queried is using a Japanese locale or collation.

Cause

Attribute queries in ArcGIS Pro are passed as-is to SQL Server. SQL Server treats character strings as single-byte, automatically converting them to the default single-byte code page that it is configured to use.

If the character string being queried contains non-local characters – in other words, characters from an alphabet that is different from the locale set for the database – the query returns invalid results.

Solution or Workaround

To pass a Unicode string to a SQL Server database, use the N prefix. Place the letter N immediately before the character string. This is shown in the example below. This tells SQL Server to treat the string as Unicode and not single-byte characters. The N prefix can only be applied when authoring the query in SQL mode as shown in the screenshot below. Use the SQL toggle button to switch from the interactive query builder to SQL mode.

Country_local = N'日本'

Note:
If data is stored as Unicode, but does not contain any non-local characters, then it is not necessary to use the N prefix.
The examples above use the query builder available when selecting by attributes. This solution applies anywhere SQL expressions are used and when authoring Query Layers.