IBM PureData System for Analytics, Version 7.1

Character string data types

Character strings are the most commonly used data types. They can hold any sequence of letters, digits, punctuation, and other valid characters.

Typical character strings are names, descriptions, and mailing addresses. Although you can store any value in a character string, use character strings only if other data types are inappropriate. Other data types provide better data validation and more compact storage.

The following table describes the character string data types.

Table 1. Character string data types
Type	Value	Disk usage
Fixed length, character(n) (alias char(n))	Fixed length, blank padded to length n. The default value of n is 1. The maximum character string size is 64,000.	If n is equal to 16 or less (n bytes). If n is greater than 16, disk usage is the same as varchar(n).
Variable length, character varying(n) (alias varchar(n))	Variable length to a maximum length of n. No blank padding, stored as entered. The maximum character string size is 64,000.	N+2 or fewer bytes depending on the actual data.
Fixed length, Unicode (alias nchar(n))	Fixed length, blank padded to length n. The maximum length is 16,000 characters.	For more information, see The data types.
Variable length, Unicode (alias nvarchar(n))	Variable length to a maximum length of n. The maximum length is 16,000 characters.	For more information, see The data types.

To determine the optimal character data type, type the following SQL command:

system.admin(admin)=> SELECT 
MAX(LENGTH(TRIM(column_name))),AVG(LENGTH(TRIM(column_name)))FROM 
table_name;

When you are selecting a character data type, consider the following situations:

If the data is exclusively numeric, use an integer data type instead of a character data type. For example, 11212345 can be defined as a VARCHAR or a bigint. Select a bigint, especially if you are using the column for distribution or joins.
If, when you are converting source date, the MAX length is less than the CHAR size, use a CHAR instead of VARCHAR. If the AVG length +2 is less than the CHAR size, use a VARCHAR instead of a CHAR.
Comparing numbers with string data types can sometimes produce unpredictable results. Use the to_number conversion function to convert the string to a number. For example:
```
   where to_number(<varchar-column>, '9999') > <integercolumn>
```