Comparisons between CHAR and NCHAR

When a comparison is performed between a value of CHAR type (CHAR, VARCHAR, LONG VARCHAR) and a value of NCHAR type (NCHAR, NVARCHAR, LONG NVARCHAR), SQL Anywhere uses inference rules to determine the type in which the comparison should be performed. Generally, if one value is based on a column reference and the other is not, the comparison is performed in the type of the value containing the column reference.

The inference rules revolve around whether a value is based on a column reference. In the case where one value is a variable, a host variable, a literal constant, or a complex expression not based on a column reference and the other value is based on a column reference, then the constant-based value is implicitly cast to the type of the column-based value.

Following are the inference rules, in the order in which they are applied:

  • If the NCHAR value is based on a column reference, the CHAR value is implicitly cast to NCHAR, and the comparison is done as NCHAR. This includes the case where both the NCHAR and CHAR value are based on column references.
  • Else if the NCHAR value is not based on a column reference, and the CHAR value is based on a column reference, the NCHAR value is implicitly cast to CHAR, and the comparison is done as CHAR.

    It is important to consider the setting for the on_charset_conversion_failure option if you anticipate NCHAR to CHAR conversions since this option controls behavior if an NCHAR character cannot be represented in the CHAR character set. For further explanation, see Converting NCHAR to CHAR.

  • Else if neither value is based on a column reference, then the CHAR value is implicitly cast to NCHAR and the comparison is done as NCHAR.
Examples

The condition Employees.GivenName = N'Susan' compares a CHAR column (Employees.GivenName) to the literal N'Susan'. The value N'Susan' is cast to CHAR, and the comparison is performed as if it had been written as: 

Employees.GivenName  = CAST( N'Susan' AS CHAR )

Alternatively, the condition Employees.GivenName = T.nchar_column would find that the value T.nchar_column can not be cast to CHAR. The comparison would be performed as if it were written as follows, and an index on Employees.GivenName can not be used: 

CAST( Employees.GivenName AS NCHAR ) = T.nchar_column;
See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0