Varchar to Decimal conversion in DB2 with empty and null values

I have a varchar field AMOUNT in DB2.

Possible table state values:

ID            AMOUNT
-------------------------
1             123.4578
2              NULL
2             123.78
1            -8562.85441
2
1             0.0
-------------------------

Column AMOUNT can be empty as the second last row above depicts.

I want to do a SUM over the AMOUNT group by ID in a query.

The result SUM should be DECIMAL(16,2).

What will be the correct way to do that considering that the value can be both null and empty and also the number of digits after the decimal is not following any fixed format?

It's a duplicate of this question, but the answers given are not complete and not accepted.

• basically if(amount is null OR amount = '') then 0 else amount, in pseudo-code. this column should be done numerically, though. a numeric column wouldn't allow an "empty" value, which is a string. a number is either null, or an number. Aug 11 '14 at 15:53

I think the code you want is:

select (case when amount <> '' then cast(amount as float) end)
from table t;

You don't have to worry about NULL values. sum() ignores them. If you want to get 0 if all values are NULL/blank, then add else 0 to the case statement.

If you are concerned about other non-numeric values, you can try:

select (case when amount <> '' and
not regexp_like(amount, '[^0-9.]', '')
then cast(amount as float) end
from table t;
• Getting below error: Error: DB2 SQL Error: SQLCODE=-461, SQLSTATE=42846, SQLERRMC=SYSIBM.CHARACTER;SYSIBM.DOUBLE, DRIVER=3.50.152 Aug 11 '14 at 16:16
• @Vicky . . . You should edit your question and show the code that you are running. Aug 11 '14 at 22:06
• The translate statements is not correct. All DB2 translate specifications I am able to find are TRANSLATE( string_expression [, to_string [, from_string [, pad ]]] ). I am not sure how it works on other systems but IBMi will not take and empty string for to or from string. Feb 16 '21 at 0:05
• @Peter . . . That is a really good point. I replaced it regexp_like(). Feb 16 '21 at 1:12
CAST(CASE WHEN ISNULL(AMOUNT, '') = '' THEN NULL ELSE REPLACE(AMOUNT, ',', '.') END AS DECIMAL (16,2))  AS AMOUNT

or

CAST (123.4578 AS DECIMAL (16,2))
+ CAST (NULL AS DECIMAL (16,2))
+ CAST (123.78 AS DECIMAL (16,2))
+ CAST (-8562.85441 AS DECIMAL (16,2))

= RESULT DECIMAL(16,2)
{"ft_lang_label":"__label__en","ft_lang_prob":0.82859296,"math_prob":0.9034187,"size":857,"snap":"2022-05-2022-21","text_gpt3_token_len":218,"char_repetition_ratio":0.13833529,"word_repetition_ratio":0.0,"special_character_ratio":0.31738624,"punctuation_ratio":0.12222222,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98967826,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-01-17T22:46:44Z\",\"WARC-Record-ID\":\"<urn:uuid:e48d6277-b5dc-469d-b69c-744f48d65f58>\",\"Content-Length\":\"151802\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:5b40085f-eeaf-4ef0-a9ca-95a12c095e51>\",\"WARC-Concurrent-To\":\"<urn:uuid:7f5b8166-e509-4ec2-8c1d-096298b81f81>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://stackoverflow.com/questions/25247336/varchar-to-decimal-conversion-in-db2-with-empty-and-null-values/62915963#62915963\",\"WARC-Payload-Digest\":\"sha1:63Y7DBLUZDTJLH74XQDBR67AHZU76Q3Q\",\"WARC-Block-Digest\":\"sha1:Q3WZTWRD56UY6ZRRRHI7JTDHPGLMXOUP\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-05/CC-MAIN-2022-05_segments_1642320300624.10_warc_CC-MAIN-20220117212242-20220118002242-00624.warc.gz\"}"}