VARCHAR2 and NVARCHAR2 data types

The VARCHAR2 and NVARCHAR2 data types support applications that use the Oracle VARCHAR2 and NVARCHAR2 data types. This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

Effects

When the VARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped to the VARCHAR data type. The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32. Similarly, when the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped following the same rules as the NVARCHAR data type.

Character string literals can have a data type of CHAR or VARCHAR, depending on the length and the string units of the environment. Character string literals up to the maximum length of a CHAR in the string units of the environment (255 OCTETS or 63 CODEUNITS32) have a data type of CHAR. Character string literals longer than the maximum length of a CHAR in the string units of the environment have a data type of VARCHAR.

Comparisons involving varying-length string types use non-padded comparison semantics, and comparisons with only fixed-length string types continue to use blank-padded comparison semantics, with two exceptions:
  • Comparisons involving string column information from catalog views always use the IDENTITY collation with blank-padded comparison semantics, regardless of the database collation.
  • String comparisons involving a data type with the FOR BIT DATA attribute always use the IDENTITY collation with blank-padded comparison semantics.
The rules for result data types are modified as follows:
Table 1. Modified rules for result data types that involve character strings
If one operand is... And the other operand is... The data type of the result is...
CHAR(x) CHAR(x) CHAR(x)
CHAR(x) CHAR(y) VARCHAR(z), where x != y and z = max(x,y)
GRAPHIC(x) GRAPHIC(x) GRAPHIC(x)
GRAPHIC(x) GRAPHIC(y) VARGRAPHIC(z), where x != y and z = max(x,y)
GRAPHIC(x) CHAR(y) VARGRAPHIC(z), where z = max(x,y)

If the result type for the IN list of an IN predicate would resolve to a fixed-length string data type and the left operand of the IN predicate is a varying-length string data type, the IN list expressions are treated as having a varying-length string data type.

Character and binary string values (other than LOB values) with a length of zero are generally treated as null values. An assignment or cast of an empty string value to CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, or VARBINARY produces a null value.

Functions that return character or binary string arguments, or that are based on parameters with character or binary string data types, also treat empty string CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, or VARBINARY values as null values. Therefore, the result of some built-in functions and casts that return character or graphic string can be null even when all of the arguments are not null. Special considerations apply for some functions, as follows:
  • CONCAT function and the concatenation operator. A null or empty string value is ignored in the concatenated result. The result type of the concatenation is shown in the following table.
    Table 2. Data Type and lengths of concatenated operands
    Operands Combined length attributes1 Result1
    CHAR(A) CHAR(B) <=S CHAR(A+B)
    CHAR(A) CHAR(B) >S VARCHAR(A+B)
    CHAR(A) VARCHAR(B) - VARCHAR(MIN(A+B, W))
    VARCHAR(A) VARCHAR(B) - VARCHAR(MIN(A+B, W))
    CLOB(A) CHAR(B) - CLOB(MIN(A+B, X))
    CLOB(A) VARCHAR(B) - CLOB(MIN(A+B X))
    CLOB(A) CLOB(B)   CLOB(MIN(A+B, X))
    GRAPHIC(A) GRAPHIC(B) <=T GRAPHIC(A+B)
    GRAPHIC(A) GRAPHIC(B) >T VARGRAPHIC(A+B)
    GRAPHIC(A) VARGRAPHIC(B) - VARGRAPHIC(MIN(A+B, Y))
    VARGRAPHIC(A) VARGRAPHIC(B) - VARGRAPHIC(MIN(A+B, Y))
    DBCLOB(A) CHAR(B) - DBCLOB(MIN(A+B, Z))
    DBCLOB(A) VARCHAR(B) - DBCLOB(MIN(A+B, Z))
    DBCLOB(A) DCLOB(B)   DBCLOB(MIN(A+B, Z))
    1. See the following table for values for italicized variables.
    Table 3. The italicized variables in the previous table have the following values
    Variable If no operand has string units of
    CHAR (or CODEUNITS32)    		 If either operand has string units of
    CHAR (or CODEUNITS32
    S 255 63
    T 127 63
    W 32672 8168
    X 2G 536870911
    Y 16336 8168
    Z 1G 536870911
  • DECODE function. If the first result expression is an untyped null it is assumed to be VARCHAR(0). If the first result expression is CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
  • GREATEST function. If the first expression is CHAR, BINARY, or GRAPHIC, it is promoted to VARCHAR, VARBINARY or VARGRAPHIC.
  • INSERT function. A null value or empty string as the fourth argument results in deletion of the number of bytes indicated by the third argument, beginning at the byte position indicated by the second argument from the first argument.
  • LEAST function. If the first expression is CHAR, BINARY, or GRAPHIC, it is promoted to VARCHAR, VARBINARY or VARGRAPHIC.
  • LENGTH function. The value returned by the LENGTH function is the number of bytes in the character string. An empty string value returns the null value.
  • NVL function. If the first expression is CHAR, BINARY, or GRAPHIC, it is promoted to VARCHAR, VARBINARY, or VARGRAPHIC.
  • NVL2 function. If the result expression is an untyped null it is assumed to be VARCHAR(0). If the result expression is CHAR, BINARY, or GRAPHIC, it is promoted to VARCHAR, VARBINARY, or VARGRAPHIC.
  • REGEXP_REPLACE function. A null value or empty string as the third argument is treated as an empty string. Nothing replaces the string that is removed from the source string that is based on the matched string that is determined by the other arguments.
  • REPLACE function. If all of the argument values have a data type of CHAR, VARCHAR, , BINARY, VARBINARY, GRAPHIC, or VARGRAPHIC, then:
    • A null value or empty string as the second argument is treated as an empty string, and consequently the first argument is returned as the result
    • A null value or empty string as the third argument is treated as an empty string, and nothing replaces the string that is removed from the source string by the second argument.

    If any argument value has a data type of CLOB or BLOB and any argument is the null value, the result is the null value. All three arguments of the REPLACE function must be specified.

  • SUBSTR function. References to SUBSTR are replaced with the following function invocation based on the first argument:
    • SUBSTRB when the first argument is a binary string or character string with string units defined as OCTETS.
    • SUBSTR2 when the first argument is a graphic string with string units defined as CODEUNITS16.
    • SUBSTR4 when the first argument is a character string or graphic string with string units defined as CODEUNITS32.
  • TO_CHAR function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point.
  • TO_NCHAR function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point.
  • TRANSLATE function. The from-string-exp is the second argument, and the to-string-exp is the third argument. If the to-string-exp is shorter than the from-string-exp, the extra characters in the from-string-exp that are found in the char-string-exp (the first argument) are removed; that is, the default pad-char argument is effectively an empty string, unless a different pad character is specified in the fourth argument.
  • TRIM function. If the trim character argument of a TRIM function invocation is a null value or an empty string, the function returns a null value.
  • VARCHAR_FORMAT function. If two arguments are specified and the first argument is a string, the first argument is cast to a decimal floating point.

In the ALTER TABLE statement or the CREATE TABLE statement, when a DEFAULT clause is specified without an explicit value for a column defined with the VARCHAR or the VARGRAPHIC data type, the default value is a blank character. If the column is defined with the VARBINARY data type, the default value is a hexadecimal zero.

Empty strings in catalog view columns are converted to a blank character. For example:
  • SYSCAT.DATAPARTITIONS.STATUS has a single blank character when the data partition is visible.
  • SYSCAT.PACKAGES.PKGVERSION has a single blank character when the package version has not been explicitly set.
  • SYSCAT.ROUTINES.COMPILE_OPTIONS has a null value when compile options have not been set.

If SQL statements use parameter markers, a data type conversion that affects VARCHAR2 usage can occur. For example, if the input value is a VARCHAR of length zero and it is converted to a LOB, the result will be a null value. However, if the input value is a LOB of length zero and it is converted to a LOB, the result will be a LOB of length zero. The data type of the input value can be affected by deferred prepare.

When defining a data type, CHAR can be used as a synonym for CODEUNITS32, and BYTE can be used as a synonym for OCTETS.

Restrictions

The VARCHAR2 data type and associated character string processing support have the following restrictions:
  • The VARCHAR2 length attribute qualifier CHAR is accepted only in a Unicode database as a synonym for CODEUNITS32.
  • The LONG VARCHAR and LONG VARGRAPHIC data types are not supported (but are not explicitly blocked).
  • Without specifying the maximum length for a VARCHAR2 parameter, the default is 4000 bytes.
  • NLSCHAR collation, used for sorting characters in a TIS620-1 (code page 874) Thai database, is not supported when setting DB2_COMPATIBILITY_VECTOR=ORA.