'DB2 SQL How do I turn a number into a string then concatenate a percent sign onto it?

I would like to display 71% in my RESULTS field. I have rounded the decimal percentage first (it was 71.04), now I want to turn it into a string so that I can concatenate the '%' onto it. This should be simple but everything I've searched for and tried has failed. Below are the 2 most promising versions of what I have tried and the result of each one:

    CHAR
    (
    ROUND
        (
        (COUNT(ONTIMETOPDD) * 100.0) / 
            (
            SELECT COUNT(ONTIMETOPDD)
            FROM WG4
            WHERE APPLICABLEDELIVCOLUMN <> 'NO DELIVERY DATE'
            )
        ,0)
    )
|| '%'
AS RESULTS

Result: No error message, just a blank field. I also tried this with the concatenation line commented out. That gave me the number 71 justified on the right side of the column which makes me believe it was not converted to a string.

    CAST
    (
    ROUND
        (
        (COUNT(ONTIMETOPDD) * 100.0) / 
            (
            SELECT COUNT(ONTIMETOPDD)
            FROM WG4
            WHERE APPLICABLEDELIVCOLUMN <> 'NO DELIVERY DATE'
            )
        ,0)
    AS CHAR)
|| '%'
AS RESULTS

Result: Error message SQL0433 - Significant data truncated during CAST to character. (#-433)

I would really appreciate it if someone can point me in the right direction.

sqldb2


Solution 1:[1]

CHAR is a fixed length character value. Which includes leading/trailing blanks.

Given that you what to concatenate a % sign, VARCHAR would be a better choice.

ROUND() does not change the scale of the number being rounded, it simply makes the appropriate digits zero.

So if you really wanted to use round, you'll need to another function to change the scale of the value to 0 prior to the conversion to VARCHAR. INT() is my personal favorite. 

select .7184, varchar(int(round(.7184, 2) * 100)) concat '%' 
from sysibm.sysdummy1

If you are ok with simple truncation, you can just use INT() without the round.

select .7184, varchar(int(.7184 * 100)) concat '%'   
from sysibm.sysdummy1

Solution 2:[2]

I prefer to use TO_CHAR() in all cases: 

TO_CHAR(
    (SELECT COUNT(ONTIMETOPDD)
     FROM WG4
     WHERE APPLICABLEDELIVCOLUMN <> 'NO DELIVERY DATE'
    ),
    '99'
) || '%'

Solution 3:[3]

The percentage ratio is a DECIMAL type prior to the conversion to a character column, so try casting it to a SMALLINT first in order to get rid of the decimal point. After that, casting to VARCHAR instead of CHAR will eliminate the trailing spaces for values between 0 and 99.

LTRIM 
(
    CAST 
    (
        SMALLINT 
        (
            ROUND 
            (
                (COUNT(ONTIMETOPDD) * 100.0) / 
                (
                    SELECT COUNT(ONTIMETOPDD)
                    FROM WG4
                    WHERE APPLICABLEDELIVCOLUMN <> 'NO DELIVERY DATE'
                )
            ,0)
        )
    AS VARCHAR(4) 
    )
)
|| '%'
AS RESULTS

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charles
Solution 2 Andy
Solution 3 Fabian N.

Related Questions