Numeric Data Types
Numeric Data Types#
Numeric data types are used to store zero as well as positive and negative numbers having fixed values. Altibase supports the following numeric types:
- BIGINT
- DECIMAL
- DOUBLE
- FLOAT
- INTEGER
- NUMBER
- NUMERIC
- REAL
- SMALLINT
BIGINT#
Syntax Diagram#
Syntax#
BIGINT
Description#
This is an 8-byte integer data type.
It is equivalent to the "long" (on 64-bit systems) and "long long" (on 32-bit systems) types in the C language.
Range: -263 + 1(-9223372036854775807) ~ 263 – 1(9223372036854775807)
DECIMAL#
Syntax Diagram#
Syntax#
DECIMAL [(precision[, scale])]
Description#
This data type is the same as the NUMERIC type.
DOUBLE#
Syntax Diagram#
Syntax#
DOUBLE
Description#
This is an 8-byte floating-point numeric data type.
It is the same as the "double" type in the C language.
FLOAT#
Syntax Diagram#
Syntax#
FLOAT [(precision)]
Description#
This is a floating-point numeric data type that can store a value ranging from -1E+120 to 1E+120.
Precision is the number of significant digits, that is, the number of digits used to express the mantissa of the floating-point number.
Precision can range from 1 to 38. If it is not expressly specified, the default precision is 38.
INTEGER#
Syntax Diagram#
Syntax#
INTEGER
Description#
This is an integer data type that is 4 bytes in size.
It is the same as the "int" data type in the C language.
It can have an integer value ranging from -2,147,483,647 to 2,147,483,647.
NUMBER#
Syntax Diagram#
Syntax#
NUMBER [(precision, scale)]
Description#
This is an alias of the NUMERIC data type. However, when precision and scale are not defined, they are the same as for the FLOAT data type. The FLOAT data type is rounded to the 39th digit.
NUMERIC#
Syntax Diagram#
Syntax#
NUMERIC [(precision, scale)]
Description#
NUMERIC is a fixed decimal data type that can contain a total number of significant digits up to the value specified using precision and a number of digits to the right of the decimal place up to the value specified using scale. In contrast to the FLOAT data type, which is a floating-point numerical data type used for representing real numbers, when both precision and scale are omitted from a NUMERIC data type declaration, precision defaults to 38 and scale to 0, i.e.
- Precision can be specified within the range from 1 to 38.
- Scale can be specified within the range from -84 to 128.
- If precision is omitted, the default is 38.
- If scale is omitted, the default is 0.
The following shows the respective values that would result when the input value 1234567.89 is converted to the NUMERIC types defined as shown.
| NUMERIC | Converted Value |
|---|---|
| NUMERIC | 1234568 |
| NUMERIC(9) | 1234568 |
| NUMERIC(9, 2) | 1234567.89 |
| NUMERIC(9, 1) | 1234567.9 |
| NUMERIC(6) | Precision exceeded |
| NUMERIC(7, -2) | 1234500 |
| NUMERIC(7, 2) | Precision exceeded |
REAL#
Syntax Diagram#
Syntax#
REAL
Description#
The data type is used to store 4-byte floating-point numeric values.
It is the same as the "float" type in the C language.
SMALLINT#
Syntax Diagram#
Syntax#
SMALLINT
Description#
This data type is used to store 2-byte integer values.
It is the same as the "short" type in the C language.
It can be used to store integers ranging from -215 + 1(-32,767)에서 215 - 1(32,767) inclusive
Number Format Model#
When data are converted using typecasting functions such as TO_CHAR or TO_NUMBER, numeric data can be specified in the following formats. A number format model consists of one or more elements that represent a number. In this section, each of these elements will be explained with reference to examples showing the related number formats.
, (Comma)#
Descriptions#
Outputs a comma at the specified position.
Commas can be used multiple times.
Restriction#
A comma cannot be placed at the end of a number, to the right of a decimal point, or at the very beginning of a number.
Example#
iSQL> SELECT TO_CHAR (1234, '99,99') FROM dual;
TO_CHAR (1234, '99,99')
---------------------------
12,34
1 row selected.
iSQL> SELECT TO_NUMBER ( '12,34', '99,99') FROM dual;
TO_NUMBER ( '12,34', '99,99')
--------------------------------
1234
1 row selected.
. (Decimal Point)#
Description#
Adds a decimal point at the specified position
Restriction#
Only one decimal point can be used within a number
Example#
iSQL> SELECT TO_CHAR (1.234, '99.999') FROM dual;
TO_CHAR (1.234, '99.999')
-----------------------------
1.234
1 row selected.
iSQL> SELECT TO_NUMBER ( '1.234', '99.999') FROM dual;
TO_NUMBER ( '1.234', '99.999')
---------------------------------
1.234
1 row selected.
$#
Description#
Prepends the $ sign to a number
Example#
iSQL> SELECT TO_CHAR (123, '$9999') FROM dual;
TO_CHAR (123, '$9999')
--------------------------
$123
1 row selected.
iSQL> SELECT TO_NUMBER ( '$0123', '09$99') FROM dual;
TO_NUMBER ( '$0123', '09$99')
--------------------------------
123
1 row selected.
0(Numeral 0)#
Description#
If the number of significant digits to be output exceeds the number of digits in the number that is input, 0's (zeroes) are prepended to the number before it is returned. In all other aspects, this element is the same as the "9" element, which is described below.
Example#
iSQL> SELECT TO_CHAR (123, '0999') FROM dual;
TO_CHAR (123, '0999')
-------------------------
0123
9(Numeral 9)#
Description#
Uses the numeral 9 to indicate the number of digits to output. If the number of 9's is greater than the number of digits in the number that is input, the space to the left of the number is padded with blank spaces before the number is output. If the number of 9's to the left of the decimal point is less than the number of digits to the left of the decimal point in the input number, the pound sign ("#") is repeatedly output. The number of pound signs that are output is the number of characters in the user-defined format plus one (a sign character). A decimal point placed in between 9's separates the integer and fractional parts of a number
When there are digits to the right of the decimal point in the first argument, i.e. when the input number has a fractional part, but the user-defined format either has no fractional part or has a fractional part with a smaller number of decimal places than the input number, the input number is rounded off to the number of decimal places in the user-defined format
Example#
iSQL> SELECT TO_CHAR (123, '99999') FROM dual;
TO_CHAR (123, '99999')
--------------------------
123
iSQL> SELECT TO_CHAR (123.55, '999') FROM dual;
TO_CHAR (123.55, '999')
---------------------------
124
1 row selected.
iSQL> SELECT TO_CHAR (123.4567, '999999') FROM dual;
TO_CHAR (123.4567, '999999')
--------------------------------
123
1 row selected.
iSQL> SELECT TO_CHAR (1234.578, '9999.99') FROM dual;
TO_CHAR (1234.578, '9999.99')
---------------------------------
1234.58
1 row selected.
iSQL> SELECT TO_CHAR (1234.578, '999.99999') FROM dual;
TO_CHAR (1234.578, '999.99999')
-----------------------------------
########
1 row selected.
iSQL> SELECT TO_NUMBER ( '123', '99999') FROM dual;
TO_NUMBER ( '123', '99999')
------------------------------
123
1 row selected.
iSQL> SELECT TO_NUMBER ( '1234.58', '9999.99') FROM dual;
TO_NUMBER ( '1234.58', '9999.99')
------------------------------------
1234.58
1 row selected.
FM#
Description#
Removes spaces or zeroes from the left part of the output string.
Example#
iSQL> select to_char(00123.100,'99999.999') from dual;
TO_CHAR(00123.100,'99999.999')
----------------------------------
123.100
1 row selected.
iSQL> select to_char(00123.100,'FM99999.999') from dual;
TO_CHAR(00123.100,'FM99999.999')
------------------------------------
123.100
1 row selected.
B#
Description#
0's (zeroes) in the integer part of the fixed-point number are replaced with blank spaces.
Example#
iSQL> SELECT TO_CHAR (0.4, 'B9') FROM T1;
TO_CHAR (0.4, 'B9')
-----------------------
1 row selected.
C#
Description#
Returns the ISO currency symbol(the value set for the NLS_ISO_CURRENCY property) in the specified position.
Example#
iSQL> SELECT TO_CHAR (4000, 'C9999') FROM dual;
TO_CHAR (4000, 'C9999')
---------------------------
KRW4000
1 row selected.
D#
Description#
Returns the decimal character(the value set for the NLS_NUMERIC_CHARACTER property) in the specified position. The default value is a period(.).
Restriction#
Only one decimal character can be included when specifying a number format model.
Example#
iSQL> SELECT TO_CHAR (24.06, '99D99') FROM dual;
TO_CHAR (24.06, '99D99')
----------------------------
24.06
1 row selected.
iSQL> SELECT TO_CHAR (206, '999D99') FROM dual;
TO_CHAR (206, '999D99')
---------------------------
206.00
1 row selected.
EEEE#
Description#
Displays the input number in exponential notation
Restriction#
EEEE should always be at the right end. It is possible to be at the left rather than S, PR, or MI. It cannot be used with commas. In addition, it cannot be used in the TO_NUMBER function.
Example#
iSQL> SELECT TO_CHAR (1234, '9.9EEEE') FROM dual;
TO_CHAR (1234, '9.9EEEE')
-----------------------------
1.2E+03
1 row selected.
G#
Description#
Returns the group separator(the value set for the NLS_NUMERIC_CHARACTER property) in the specified position. Multiple group separators can be specified for a number format model.
Restriction#
In a number format model, a group separator cannot appear to the right of a decimal character or a period.
Example#
iSQL> SELECT TO_CHAR (2534.3, '999G999D99') FROM dual;
TO_CHAR (2534.3, '999G999D99')
----------------------------------
2,534.30
1 row selected.
L#
Description#
Returns the local currency symbol(the value set for the NLS_CURRENCY property) in the specified position.
Example#
iSQL> SELECT TO_CHAR (4000, 'L9999') FROM dual;
TO_CHAR (4000, 'L9999')
---------------------------
?4000
1 row selected.
MI#
Description#
When MI is used at the rightmost place in the number format, if the input value is negative, the minus (-) sign is output at the end of the number, rather than at the beginning. If the input value is positive, a blank space is output instead of the minus sign.
Restrictions#
MI must always be at the rightmost place in the number format. It cannot be used together with S or PR.
Example#
iSQL> SELECT TO_CHAR (-123, '999MI') FROM dual;
TO_CHAR (-123, '999MI')
---------------------------
123-
1 row selected.
iSQL> SELECT TO_NUMBER ( '123-', '999MI') FROM dual;
TO_NUMBER ( '123-', '999MI')
-------------------------------
-123
1 row selected.
PR#
Description#
When PR is used at the rightmost place in the number format, if the input value is negative, the value is output in the form of "<number>", rather than using the minus ("-") sign.
Restriction#
PR must always be at the rightmost place in the number format. It cannot be used together with S or MI.
Example#
iSQL> SELECT TO_CHAR (-123, '999PR') FROM dual;
TO_CHAR (-123, '999PR')
---------------------------
<123>
1 row selected.
iSQL> SELECT TO_NUMBER ( '<123>', '999PR') FROM dual;
TO_NUMBER ( '<123>', '999PR')
--------------------------------
-123
1 row selected.
RN#
Description#
Converts an input number to Roman numerals. The valid input range is from 1 to 3,999. If the lower-case letters "rn" are used in the number format, lower-case Roman numerals are output.
Restriction#
RN cannot be used with any other number format elements or with the TO_NUMBER function.
Example#
iSQL> SELECT TO_CHAR (14, 'RN') FROM dual;
TO_CHAR (14, 'RN')
----------------------
XIV
1 row selected.
S#
Description#
When S is placed at the beginning or end of the number format, a plus ("+") or minus ("-") sign is output at the same position, corresponding to the sign of the input number.
Restriction#
S can be placed at the beginning or end of the number format. It cannot be used with MI or PR.
Example#
iSQL> SELECT TO_CHAR (123, 'S999.99') FROM dual;
TO_CHAR (123, 'S999.99')
----------------------------
+123.00
1 row selected.
iSQL> SELECT TO_CHAR (-123, '999.99S') FROM dual;
TO_CHAR (-123, '999.99S')
-----------------------------
123.00-
1 row selected.
iSQL> SELECT TO_NUMBER ( '+123', 'S999.99') FROM dual;
TO_NUMBER ( '+123', 'S999.99')
---------------------------------
123
1 row selected.
iSQL> SELECT TO_NUMBER ( '123.00-', '999.99S') FROM dual;
TO_NUMBER ( '123.00-', '999.99S')
------------------------------------
-123
1 row selected.
V#
Description#
The input number is multiplied by 10 to the power of the number of 9's after V. The number of 9's before V represents the number of significant digits to return from the input number.
Restriction#
V cannot be used with a decimal point, and cannot be used with the TO_NUMBER function.
Example#
iSQL> SELECT TO_CHAR (12, '99V99') FROM dual;
TO_CHAR (12, '99V99')
-------------------------
1200
1 row selected.
iSQL> SELECT TO_CHAR (1200, '99V99') FROM dual;
TO_CHAR (1200, '99V99')
---------------------------
#####
1 row selected.
iSQL> SELECT TO_CHAR (-123.456, '999V999EEEEMI') from dual;
TO_CHAR (-123.456, '999V999EEEEMI')
---------------------------------------
1235E+02-
1 row selected.
XXXX#
Description#
Converts the input number to a hexadecimal number. If the input number is not an integer, it is rounded off before being converted to a hexadecimal number. Specifying "xxxx" in lower-case returns the letters in the hexadecimal number in lower-case.
Restriction#
XXXX cannot be used with other number format elements. The number to be converted must be greater than 0 (zero).
Example#
iSQL> SELECT TO_CHAR (123, 'XXXX') FROM dual;
TO_CHAR (123, 'XXXX')
-------------------------
7B
1 row selected.
iSQL> SELECT TO_NUMBER ('ABC', 'XXXX') FROM dual;
TO_NUMBER ('ABC', 'XXXX')
----------------------------
2748
1 row selected.