# Thread: SQL to convert Char to Decimal

1. Registered User
Join Date
Nov 2006
Posts
31

## Unanswered: SQL to convert Char to Decimal

Hi All,
I have a column 'AMOUNT' defined as CHAR(18). Below is sample data and I need to find out the sum of the AMOUNT column. This column has negative numbers, nulls and decimal numbers. I tried to use CAST(AMOUNT AS DECIMAL) but erroring out with SQL0413N. Is there a way to avoid these errors?

11374.00
1.882138
200
25
-
565
7.324088
-42.97209
854
-
6
6
-
350
-6
15.380584
1900
90
2166
-77.976
-
90
-77.976
50
92.4
109.864973

2. Registered User
Join Date
Jan 2003
Posts
4,310
Try adding the precision and scale to the cast as decimal. e.g. CAST(AMOUNT as DECIMAL(31,8)).

Andy

3. Registered User
Join Date
Nov 2006
Posts
31
Andy,
I have tried to use CAST(AMOUNT as DECIMAL(31,8)) but throwing the below error.
SQL0420N Invalid character found in a character string argument of the function "DECIMAL". SQLSTATE=22018

Sathya,
Above is just a sample of records. This table has more than 48 Million records and I am using a multiple joins to filter the number of records.
Last edited by samu78nyc; 11-05-09 at 10:48.

4. Registered User
Join Date
Sep 2008
Location
Posts
658
db2 values cast('12.34' as decimal(31,8))

1
---------------------------------
12.34000000

1 record(s) selected.
The casting is fine this way. Please Check value of "AMOUNT".

5. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
He has to use STRIP(AMOUNT) function before casting.

Lenny

6. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
-413 OVERFLOW OCCURRED DURING NUMERIC DATA TYPE CONVERSION

Explanation: During processing of the SQL statement, an overflow
condition arose when converting from one numeric type to another. Numeric
conversion is performed according to the standard rules of SQL.

System Action: The statement cannot be processed. No data was retrieved,
updated, or deleted.

Programmer Response: Examine the syntax of the SQL statement to determine
the cause of the error. If the problem is data-dependent, it might be
necessary to examine the data processed at the time of the error.

Lenny

P.S. He can use: DOUBLE(STRIP(AMOUNT))
Last edited by Lenny77; 11-05-09 at 11:39.

7. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
The problem is if any of the rows being processed has an invalid string, the error will be raised. What can be done is to wrap the conversion in a stored procedure where you define an exception handler. Then use a UDF that calls the stored procedure. Search for "soft_int" to find an example how to do this for integers.

8. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
You can use this query, which is for select numeric data, only:

Code:
```select column1
from
(select   case when substr(strip(column1), length(strip( column1)), 1)
in ('+', '-')
then
substr(strip(column1), length(strip( column1)), 1)
||
substr(strip(column1), 1, length(strip(column1)) - 1)
when substr(strip( column1), 1, 1) in ('+', '-')
then
substr(strip(column1), 1, 1)
||
strip(substr(strip(column1), 2))
else    strip(column1)
end AS column1
from table1
where Translate(column1, ' ', '0123456789+-.') = ' '
and  column1 > Translate(column1, ' ', '0123456789')
) ii
where
replace( column1, ' ', '') = column1
and
length(replace( column1, '+', '')) >= length(column1) - 1
and
length(replace( column1, '-', '')) >= length(column1) - 1
and
length(replace( column1, '.', '')) >= length(column1) - 1
and
posstr(column1, '-')  in (0, 1)
and
posstr(column1, '+')  in (0, 1)
and not exists
(select * from sysibm.sysdummy1
where
posstr(column1, '-') > 0
and
posstr(column1, '+') > 0)```
You have to change "column1" to "amount" and "table1" on "your_table_name".

Lenny

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
You can use ISNUMERIC UDF in Sample UDFs for Migration to filter out non-numeric values.

Like this:
SELECT amount FROM your_table WHERE ISNUMERIC(amount) = 0;
Last edited by tonkuma; 11-05-09 at 15:53.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•