varchar

The varchar data type defines a variable-length character string with a specified maximum length.

When specifying a varchar data type, indicate the following characteristic of the type:

<length>

Number of characters that the variable or column can hold. <Length> must be greater than zero. There is no maximum allowable value for the <length> .

Character strings longer than the number of characters defined for the column or variable are truncated on the right to the length of the data type. Only the required number of characters is used to store strings shorter than <length>.

Functions are used to convert values to and from strings; to join strings together, use the concatenation operator (||). This data type allows NULL values.

The software conforms to the ANSI SQL-92 varchar standard and treats varchar data as follows:

  • Keeps trailing blanks in character values that you insert into varchar columns.

  • Keeps trailing blanks when you read from sources with string data types. If you want to remove trailing blanks from your input data, you must use the Right trim function.

  • Ignores trailing blanks when you compare varchar data in functions (Decode, Lookup Value).

  • The ANSI standard treats an empty string as a zero length varchar value.

  • When using the Equal (=) or Not Equal (!=) operators to compare a value with a NULL constant, the comparison always evaluates to FALSE. Use the IS NULL and IS NOT NULL operators to test for NULL values in the Lookup value function, and the scripting language.

The application supports reading, transforming, and loading National Language Supported (NLS) data from different language locales using the varchar data type. The application supports national character-set data types in the following databases:

Database

Version

National character-set data type

DB2

7.0 and higher

graphic, vargraphic

MS SQL Server

7.0 and higher

nchar, nvarchar

Oracle

9i and higher

nchar, nvarchar2

When the application encounters a national character-set data type in an expression, it binds the column with the data type recommended by the database.

The engine reads and loads national character-set data types seamlessly without the need for you to configure a locale for a database client and its datastore for the columns that use these data types.