 |
| SQL DECIMAL or DEC scalar function in DB2 for i SQL |
DECIMAL or DEC
The DECIMAL function returns a decimal value of either a number, or character-graphic string holding a number, integer, decimal.
Syntax of Numeric to Decimal
DECIMAL(numeric-expression, precision, scale)
Or
DEC(numeric-expression, precision, scale)
numeric-expression: expression that returns any built-in numeric data type.
precision: It is an integer constant ranges from 1 to 31. The default value depends upon the numeric-expression data type. For SMALLINT its 5, 11 for INTEGER, 19 for BIG INTEGER, 15 for FLOATING POINT or DECIMAL, 31 for DECIMAL FLOATING POINT. Optional argument.
scale: Integer constant that is greater than or equal to zero and less than or equal to precision. Default is 0. Optional argument.
Example: Numeric to Decimal
Passing only numeric expression lying in the integer range return the same value as integer
SELECT decimal(5545464) FROM sysibm.sysdummy1
Or
SELECT dec(5545464) FROM sysibm.sysdummy1
DECIMAL ( 5545464 )
5,545,464
Passing numeric expression lying in the integer range and want to get 8 digit field as a return
SELECT decimal(5545464,8) FROM sysibm.sysdummy1
Or
SELECT dec(5545464,8) FROM sysibm.sysdummy1
DECIMAL
5,545,464
Passing numeric expression lying in the integer range and want to get 8 digit field with 1 decimal places at the right as a return
SELECT decimal(5545464,8,1) FROM sysibm.sysdummy1
Or
SELECT dec(5545464,8,1) FROM sysibm.sysdummy1
DECIMAL
5,545,464.0
Syntax of String to Decimal
DECIMAL(string-expression, precision, scale, decimal-character)
Or
DEC(string-expression, precision, scale, decimal-character)
string-expression: string expression that returns valid number of length up to 255 bytes.
precision: It is an integer constant ranges from 1 to 31. Default is 15. Optional argument.
scale: Integer constant that is greater than or equal to zero and less than or equal to precision. Default is 0. Optional argument.
decimal-character: A single byte character constant for decimal digit in string expression. The character cannot be a digit, plus (+), minus (-), or blank. The default value is period (.) or comma (,).Optional argument.
Example: String to Decimal
Passing only numeric expression
SELECT decimal('5545464') FROM sysibm.sysdummy1
Or
SELECT dec('5545464') FROM sysibm.sysdummy1
DECIMAL
5,545,464
Passing numeric expression lying in the integer range and want to get 8 digit field as a return
SELECT decimal('5545464',8) FROM sysibm.sysdummy1
Or
SELECT dec('5545464',8) FROM sysibm.sysdummy1
DECIMAL
5,545,464
Passing numeric expression with precision of 8 and scale of 1 and decimal character as dot(.) in the string expression.
SELECT decimal('554546.4',8,1) FROM sysibm.sysdummy1
Or
SELECT dec('554546.4',8,1,'.') FROM sysibm.sysdummy1
DECIMAL
554,546.4
Passing numeric expression with precision of 8 and scale of 1 and decimal character as comma(,) in the string expression.
SELECT decimal('554546,4',8,1,',') FROM sysibm.sysdummy1
Or
SELECT dec('554546,4',8,1,',') FROM sysibm.sysdummy1
DECIMAL
554,546.4
