CODEWITHSUNDEEP
castingfirebirdfirebird-3.0
Fabrizio
Fabrizio

Reputation: 8043

Trailing zeros appears after casting DOUBLE PRECISION to VARCHAR

When trying to cast a DOUBLE PRECISION value to VARCHAR, I get a serie of 12 undesired trailing zeros.

For example:

SELECT CAST(CAST(2500 AS DOUBLE PRECISION) AS VARCHAR(50))
FROM rdb$database

It produces the following unexpected result:

2500.000000000000

Expected result:

2500

What is causing this problem and how can this be avoided?

Upvotes: 0

Views: 794

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109081

The immediate workaround for this problem is to use

trim(trailing '.' from trim(trailing '0' from stringvalue))

You could wrap this in a PSQL function for easier reuse.

However, this is a risky solution as very large or very small numbers will be rendered in scientific notation, and this solution would strip zeroes from the exponent. As an example, the value 1.2e30 as string will be rendered as 1.200000000000000e+30, but above solution will mangle that to 1.200000000000000e+3 (which is 1200).

As Arioch 'The already suggested in the comments, this is a problem better solved in the presentation layer of your application (which can then also take things like locale into account for things like decimal separator, thousand separator, etc).

Alternatively, you could try to find a third-party UDF or UDR (or build one yourself), that allows you to format numbers using the desired format.

Upvotes: 2

Related Questions