When using the TO_CHAR() function to format a number in Oracle Database, you can use the S format element to return its sign (i.e. a plus sign for positive values, and a minus sign for negative values).

You can also use the MI format element to return negative values with a trailing negative sign, and positive values with a trailing blank.

Example of the S Format Element

Here’s an example to demonstrate the S format element:

SELECT 
    TO_CHAR(-7, 'S9') AS "Negative",
    TO_CHAR(7, 'S9') AS "Positive"
FROM DUAL;

Result:

   Negative    Positive 
___________ ___________ 
-7          +7         

The S format element can alternatively be applied at the last position of the format model. This results in the sign being appended to the result:

SELECT 
    TO_CHAR(-7, '9S') AS "Negative",
    TO_CHAR(7, '9S') AS "Positive"
FROM DUAL;

Result:

   Negative    Positive 
___________ ___________ 
7-          7+         

But the S format element cannot appear at both ends of the format model. If it does, an error occurs:

SELECT TO_CHAR(7, 'S9S')
FROM DUAL;

Result:

Error report -
ORA-01481: invalid number format model

The MI Format Element

The MI format element a trailing negative sign in cases where the number is negative, and a trailing blank where it’s positive:

SELECT 
    TO_CHAR(-7, '9MI') AS "Negative",
    TO_CHAR(7, '9MI') AS "Positive"
FROM DUAL;

Result:

   Negative    Positive 
___________ ___________ 
7-          7          

Note that the MI format element can only appear in the last position of the format model. If it’s in any other position, an error occurs:

SELECT TO_CHAR(7, 'MI9')
FROM DUAL;

Result:

Error report -
ORA-01481: invalid number format model