Data types define acceptable values for constants, columns, host variables, functions, expressions and special registers. This section describes the data types referred to in the examples. For a full list and complete description of other data types refer to the SQL Reference.
A character string is a sequence of bytes. The length of the string is the number of bytes in the sequence. If the length is zero, the value is called the empty string.
CHAR(x) is a fixed length string. The length attribute x must be between 1 and 254, inclusive.
Varying-length character strings are of three types: VARCHAR, LONG VARCHAR, and CLOB.
VARCHAR(x) types are varying-length strings, so a string of length 9 can be inserted into VARCHAR(15) but will still have a string length of 9.
See Large Objects (LOBs) for details on CLOB.
A graphic string is a sequence of double-byte character data.
GRAPHIC(x) is a fixed length string. The length attribute x must be between 1 and 127, inclusive.
Varying-length graphic strings are of three types: VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB. See Large Objects (LOBs) for details on DBCLOB.
A binary string is a sequence of bytes. It is used to hold nontraditional data such as pictures. Binary Large OBject (BLOB) is a binary string. See Large Objects (LOBs) for more information.
All numbers have a sign and a precision. The precision is the number of bits or digits excluding the sign.
A DECIMAL is a decimal number. The position of the decimal point is determined by the precision (p) and the scale (s) of the number. Precision is the total number of digits and has to be less than 32. Scale is the number of digits in the fractional part and is always smaller than or equal to the value of precision. The decimal value defaults to precision of 5 and scale of 0 if precision and scale are not specified.
Datetime values are representations of dates, times, and timestamps (a character string of 14 digits that represents a valid date and time in the form yyyyxxddhhmmss). Datetime values can be used in certain arithmetic and string operations and are compatible with certain strings, however they are neither strings nor numbers. 1
The null value is a special value that is distinct from all non-null values. It means the absence of any other value for that column in the row. The null value exists for all data types.
The following table highlights characteristics of data types used in the
examples. All numeric data types are defined in a certain range.
The range of numeric data types is also included in this table. You can
use this table as a quick reference for proper data type usage.
| Data Type | Type | Characteristic | Example or Range |
|---|---|---|---|
| CHAR(15) | fixed-length character string | Maximum length of 254 | 'Sunny day ' |
| VARCHAR(15) | varying-length character string | Maximum length of 32672 | 'Sunny day' |
| SMALLINT | number | 2 bytes in length precision of 5 digits | range is -32768 to 32767 |
| INTEGER | number | 4 bytes in length precision of 10 digits | range is -2147483648 to 2147483647 |
| BIGINT | number | 8 bytes in length precision of 19 digits | range is -9223372036854775808 to 9223372036854775807 |
| REAL | number | single-precision floating point 32 bit approximation | range is -3.402E+38 to -1.175E-37 or 1.175E-37 to -3.402E+38 or zero |
| DOUBLE | number | double-precision floating point 64 bit approximation | range is -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308 or zero |
| DECIMAL(5,2) | number | precision is 5 scale is 2 | range is -10**31+1 to 10**31-1 |
| DATE | datetime | three-part value | 1991-10-27 |
| TIME | datetime | three-part value | 13.30.05 |
| TIMESTAMP | datetime | seven-part value | 1991-10-27-13.30.05.000000 |
See the Data Type Compatibility table in the SQL Reference for more information.