Attribute queries using Unicode strings do not work against SQL Server


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.


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

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

    • 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