In databases created in Version 9.7, leading zeros and a trailing decimal character are removed from the result of the CHAR scalar function (decimal to character). This behavior also applies to the CAST specification from decimal to character.
In previous releases, the CHAR scalar function (decimal to character) as well as the CAST specification from decimal to character returns leading zeros and a trailing decimal character in the result. The behavior is inconsistent with the VARCHAR scalar function as well as the SQL standard casting rules.
CREATE TABLE MY_TAB (C1 DEC(31,2)
INSERT INTO MY_TAB VALUES 0.20, 0.02, 1.20, 333.44
When you issue the following statement:SELECT CHAR(C1)FROM MY_TAB
In
previous releases, the following result set is returned: 1
---------------------------------
00000000000000000000000000000.20
00000000000000000000000000000.02
00000000000000000000000000001.20
00000000000000000000000000333.44
1
---------------------------------
.20
.02
1.20
333.44
CREATE TABLE MY_TAB (C1 DEC(5,0))
INSERT INTO MY_TAB VALUES 1, 4.0
SELECT CHAR(C1)FROM MY_TAB
When you issue the following
statement:SELECT CHAR(C1)FROM MY_TAB
In previous
releases, the following result set is returned: 1
-----
0001.
0004.
1
-----
1
4
If you want migrated databases to use the new format, set the dec_to_char_fmt to 'NEW'.