PROBLEM

Attribute queries using Unicode strings do not work against SQL Server

Last Published: April 25, 2020

Description

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

In the following example using a query in the ArcMap Select by Attribute dialog box, the attribute column being queried contains country names in several different languages and alphabets.
[O-Image] Thai example
No results are returned by ArcMap unless the SQL Server instance being queried is using a Thai locale or collation.

Cause

Attribute queries in ArcMap are passed as-is to SQL Server. SQL Server automatically converts any character strings to the default single-byte code page that it is configured to use.

If the character string being queried contains non-local characters, e.g. from an alphabet that is different from the locale set for the dbms, then the query will return invalid results.

Solution or Workaround

To pass a Unicode string to SQL Server, use the N prefix. Place the letter N immediately before the character string. This tells SQL Server to treat the string as Unicode and not single-byte characters.
[O-Image]Use the N prefix

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.

    Article ID:000009193

    Software:
    • Legacy Products
    • ArcMap

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options

    Discover more on this topic

    Esri Community

    Search for related information

    Training

    Find training related to this topic

    ArcGIS Ideas

    Explore ideas and give feedback