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.
|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.|
system.admin(admin)=> SELECT MAX(LENGTH(TRIM(column_name))),AVG(LENGTH(TRIM(column_name)))FROM table_name;
- 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>