Your current query gets the job done, but it’s way more convoluted than necessary. Let’s break down simpler, more readable approaches to get exactly the result you want:

Method 1: Use the FM format modifier for clean integer output #

If you only need to convert numbers with zero decimal places (like 10.00) to plain integers, pair the FM modifier with a numeric mask that omits the decimal section:

SELECT TO_CHAR(10.00, 'FM999990') FROM DUAL;

This returns '10' perfectly. The FM flag strips any default leading/trailing whitespace that Oracle adds to formatted numbers, and since we don’t include a decimal component in the mask, it automatically drops the .00 when there’s no fractional value to display.

Method 2: Flexible approach for mixed integer/decimal values #

If you need to handle both cases—like 10.00 → 10 and 10.50 → 10.5—use a regex to clean up trailing .00 or .0 after formatting:

SELECT REGEXP_REPLACE(TO_CHAR(10.00, 'FM999990.99'), '\.0*$', '') FROM DUAL;

• For 10.00, this returns '10'
• For 10.50, it returns '10.5'
• For 10.55, it returns '10.55'

The regex \.0*$ targets a decimal point followed by any number of zeros at the end of the string, replacing them with nothing to clean up the output.

Why your original query is overcomplicated #

Your existing code uses nested TRIM/RTRIM with hardcoded strings of 9s and 0s, which is hard to read and maintain. Oracle’s built-in format modifiers and regex functions are specifically designed to handle these formatting scenarios with far less code.

内容的提问来源于stack exchange，提问作者MKS