I am trying to convert a varchar field to decimal field in db2 but I am getting an error
AMOUNT field is a varchar field which needs to be converted to decimal(31,3)
Given below is my query
SELECT CAST(ROUND(TRIM(AMOUNT),3) AS DECIMAL(31,3))
FROM TABLENAME
Note: AMOUNT field (varchar) has a NULL value as well
Sample values:
7.324088
-42.97209
854
NULL
6
6
350
-6
15.380584
1900
I get the below error:
Invalid character found in a character string argument of the function
"DECFLOAT". SQLSTATE=22018
Try doing like
SELECT CAST(ROUND(COALESCE(TRIM(AMOUNT),0),3) AS DECIMAL(31,3))
FROM TABLENAME
Are you sure your input values correspond with the same locale. I mean is the minus sign a minus or the MS Word minus? What about the decimal separator? In some languages there is a comma instead of a dot.
The coalesce function retrieves the first non-null value from the parameters. In the example:
coalesce (amount, 0)
If amount is null, it will return 0.
Are you sure you are passing a null value, or a string with the characters 'NULL', because in this case you have to convert the 'NULL' to '0' with the replace function.
replace (amount, 'NULL', '0')
