Dealing with spaces when converting numbers to character strings

A reasonably common problem encountered when converting numbers to character strings is that TO_CHAR always leaves room for the minus sign even when numbers are positive. By default, TO_CHAR will leave one space in front of a number for use by a potential minus sign (-):

DECLARE b VARCHAR2(30); c VARCHAR2(30);BEGIN b := TO_CHAR(-123.4,'999.99'); c := TO_CHAR(123.4,'999.99'); DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b))); DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));END;

The output is:

:-123.40 7: 123.40 7

Notice that both converted values have the same length, seven characters, even though the positive number requires only six characters when displayed in character form. That leading space can be a big help if you are trying to get columns of numbers to line up. However, it can be a bit of a pain if for some reason you need a compact number with no spaces whatsoever.

Use the PR element, and your positive numbers will have one leading space and one trailing space to accommodate the potential enclosing angle-brackets. Spaces will be left to accommodate whatever sign-indicator you choose in your format model.

There are a couple of approaches you can take if you really need your numbers converted to characters without leading or trailing spaces. One approach is to use the TM format model element to get the "text minimum" representation of a number:

DECLARE b VARCHAR2(30); c VARCHAR2(30);BEGIN b := TO_CHAR(-123.4,'TM9'); c := TO_CHAR(123.4,'TM9'); DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b))); DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));END;

The output is:

:-123.4 6:123.4 5

The TM approach works, but it's a relatively new format element that is not available in older releases of PL/SQL (before Oracle8i). TM also doesn't allow you to specify any other formatting information. You can't, for example, specify TM999.99 in order to get a fixed two decimal digits. If you need to specify other formatting information or if TM is not available in your release of PL/SQL, you'll need to trim the results of the conversion:

DECLARE b VARCHAR2(30); c VARCHAR2(30);BEGIN b := LTRIM(TO_CHAR(-123.4,'999.99')); c := LTRIM(TO_CHAR(123.4,'999.99')); DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b))); DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));END;

The output is:

:-123.40 7:123.40 6

Here we've used LTRIM to remove any potential leading spaces, and we've successfully preserved our fixed two digits to the right of the decimal-point. Use RTRIM if you are placing the sign to the right of the number (e.g., via the MI element) or TRIM if you are using something like PR that affects both sides of the number.