Rounding is not so easy

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Rounding is not so easy

Edzard Pasma

I saw that ROUND (0) is not equal to 0. It took some time to understand this. In the first place it is that the outcome of ROUND is always of type text. That is necessarily so, as a decimally rounded number has no exact binary representaion. For instance 1.23 is really 1.229999.... So text is more correct. The second thing to understand was that when comparing two expressions, sqlite does not try any data conversion. This way '0' is never equal to 0.

Knowing this, you can just use CAST (ROUND (...) AS NUMBER) for numerical expressions.

I am mainly interested in speed, and found still an alternative not using ROUND () at all. For instance to round a number x to 1 decimal:

    CAST (x * 10. - 0.5 + (x >= 0) AS INT) / 10.

This gives a factor 2 improvement compared to CAST (ROUND (x) AS NUMBER). But sqlite is so fast that you need to repeat it 100.000 times before noticing any wait time at all.

May this be something for a new built-in function?

Thanks, Edzard Pasma

Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and Anti-Virus technology!