Subject Re: [ib-support] Converting DOUBLE PRECISION to VARCHAR
Author Helen Borrie
At 12:05 PM 18-12-02 +0200, you wrote:
>Hi Guys,
>I'm on a tight deadline and don't have much time to go through the archives,
>so I apologise if this questions has been asked before...
>I have a table with a double precision field called INCR_PREM_ADDITIONAL
>which holds a premium increase percentage....
>I would like to return in a SP a description field like...
>SHORT_DESCR = 'CPI Plus' || cast(INCR_PREM_ADDITIONAL as varchar(10)) || '%'
>And my output should look like "CPI plus 3.5%"...but I'm getting "CPI plus
>How do I do a straight forward convert from the double precision to the
>varchar without the trailing 0's to pad the varchar?

If you could always predict/prescribe a single place of decimal, you could
do this:

SHORT_DESCR = 'CPI plus ' || cast( cast (INCR_PREM_ADDITIONAL as numeric
(3,1)) as varchar(5)) || '%'

However, if you need to represent 7.25 or such, you will need to write a
stored procedure, calling some functions (SUBSTRING, SUBSTRLEN) and/or UDFs
to massage your output.