Remove 0 in decimal places when using floor-CodePudding

I'm using a select statement like below in my query:

FLOOR(CAST(amount AS DECIMAL(16))/rate/100))

Even though I want my result to be like 123, the select statement above returns 123.0 instead. How do I remove "point zero" and display simply 123?

CodePudding user response：

This is a tricky requirement, because essentially you want to display the numbers as floats, except when the decimal component be zero, in which case you want to display as an integer. We can try casting to text, followed by a regex replacement.

REGEXP_REPLACE(
    FLOOR(CAST(amount AS DECIMAL(16)) / rate / 100)::text,
    '\.0 $',
    ''
)

CodePudding user response：

Simplest way I could come up with:

select ('16'::numeric/.0013/100)::int 
123