You are here:

Unicode-Compliant SQL Queries

If you specify a SQL query that contains Unicode data, keep in mind the following:

To specify a Unicode constant, you must specify a leading N. For example: N'A Unicode string'
If you create a string user variable and use it in the source query, do not specify the N character. Insight adds it when the query runs.
Note: If you are using the Text ODBC data source (not the Unicode Text Adapter), or any data source that does not support Unicode, adding a leading "N" may cause the query to fail because they are not designed to support Unicode data.
To specify a string value with the IN operator, use single quotes around the value with no spaces. For example: IN '1','2','3'

Unicode example

If, for example, you have the following value in a user variable:

MyVar = "现金"

Note: Double-quotes around the value are required.

You can use the variable in a SQL query, as follows:

select * from BANK_ACCOUNT where AccountType = :MyVar

Insight searches the value of MyVar, find that it contains at least one Unicode character, and produce the following query:

select * from BANK_ACCOUNT where AccountType = N'现金''

Note that Insight wraps the string in single quotation marks (which are required syntax in a SQL query), then adds the leading N before the first single quote. Be sure that you do not put the "N" inside the user variable value.

For detailed information about using Unicode data in SQL queries, see the Microsoft SQL Server article: Using Unicode Data (SQL Server)

Note: For adapters that do not support SQL syntax queries, you may be able to create a Insight Custom Query.