VARCHAR2

VARCHAR2 is a non-ANSI datatype that stores varying length character strings. VARCHAR2 datatype columns can be accessed from mxci, rmxci, JDBC, and ODBC applications. You can create and manage a table with VARCHAR2 columns from MXDM. VARCHAR2 datatype is supported by:

All SQL/MX Utilities
Windows ANSI and UNICODE ODBC/MX drivers
JDBC T2 and T4 drivers

Embedded C/C++ and COBOL applications, the OSS, Linux, and HP-UX ODBC/MX drivers do not support VARCHAR2 datatype.

SQL/MX considers empty string as NULL when the associated datatype is VARCHAR2. If you insert empty string into a NOT NULL VARCHAR2 column, the operation fails with error 8421. If the assignment of empty string to NOT NULL VARCHAR2 column is detected at compilation time, compilation of statement fails with error 4122. If the associated datatype is VARCHAR2, all semantics associated with NULLs are applicable to empty strings.

SQL/MX considers the trailing spaces when comparing strings of VARCHAR2 datatype.

The ODBC and JDBC applications use the ANSI types VARCHAR or CHAR as the SQL type to bind and retrieve values. The queries using VARCHAR2 datatype cannot be cached by MFC.

The syntax of VARCHAR2 is:

VARCHAR2 (length) [CHARACTER SET char-set-name] [COLLATE DEFAULT] [UPSHIFT]

Where,

length is a positive integer that specifies the number of characters allowed in the column. You must specify a value for length.

char-set-name is the character set name, which can be ISO88591 or UCS2.

The UPSHIFT clause directs NonStop SQL/MX to upshift characters before storing them in the column.

If VARCHAR2 is used in predicates along with other datatypes, VARCHAR2 semantics is applied while evaluating the predicates.

To use a VARCHAR2 column as a partitioning key, the character set of the column must be ISO88591.

Considerations

VARCHAR2 supports ISO88591 and UCS2 character sets with the default being ISO88591.
VARCHAR2 syntax can be used in CREATE TABLE, ALTER TABLE statements, and CAST expression.

NOTE:

Online utility operations on table or index having VARCHAR2 column is not supported.

Differences between VARCHAR and VARCHAR2

Scenario	VARCHAR	VARCHAR2
Empty strings	Stored as an empty string. Not treated as NULL.	Stored and treated as NULL.
Key column values	Trailing spaces are ignored.	Trailing spaces are honored.
String Comparison	Trailing spaces are ignored.	Trailing spaces are honored.
Reserved keyword	Yes	No

Examples

The following example illustrates VARCHAR and VARCHAR2 behavior for trailing spaces. VARCHAR2 datatype honors the trailing spaces during comparisons of two strings.

>>create table vc (a varchar(10) not null primary key, b varchar(10));

--- SQL operation complete.
>>create table vc2 (a varchar2(10) not null primary key, b varchar2(10));

--- SQL operation complete.
>>insert into vc values ('abcd','abcd    ');

--- 1 row(s) inserted.
>>select * from vc where a = b;

A           B
----------  ----------

abcd        abcd

--- 1 row(s) selected.
>>insert into vc2 values ('abcd','abcd    ');

--- 1 row(s) inserted.
>>select * from vc2 where a = b;

--- 0 row(s) selected.
>>

The following example illustrates VARCHAR and VARCHAR2 behavior for key columns with trailing spaces.

>>insert into vc values ('abcd    ','abcd    ');

*** ERROR[8102] The operation is prevented by a primary key  VC_651493923_3328 on table CAT.SCH.VC.

--- 0 row(s) inserted.

>>insert into vc2 values ('abcd    ','abcd    ');

--- 1 row(s) inserted.