Why Does ORACLE TO_CHAR Function Format numbers with spaces? oracleto_char

Why Does ORACLE TO_CHAR Function Format numbers with spaces? oracleto_char

In this blog SQL challenge-how to efficiently generate the encoding I used TO_CHAR (number, '123456') because I needed to format the number as a character, such as 12 and 0012 ') in this way, the following 0000 indicates that the default value is zero. During the test, I found that TO_CHAR (number, '123') will have one more space. I am wondering why there is an extra space.

After checking the official documents, discussions with colleagues, and the information provided by netizens, I finally figured out the problem. The following is a summary of this article, hoping to help others.

In the official documentation, refer

Number Format Elements

A number format model is composed of one or more number format elements. The tables that follow list the elements of a number format model and provide some examples.

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

Note that the red part indicates that when the Number type is converted to a character, a negative Number will return a string containing a negative Number, and a positive Number will return a string containing spaces, unless the Format mode contains MI, S, or PR elements. View the length of the string returned by TO_CHAR (4, '123'). You will find that the length is 5.

   1: SQL> SELECT TO_CHAR(4, '0000') FROM DUAL;

   2:  

   3: TO_CH

   4: -----

   5:  0004

   6:  

   7: SQL> SELECT LENGTH(TO_CHAR(4, '0000')) FROM DUAL;

   8:  

   9: LENGTH(TO_CHAR(4,'0000'))

  10: -------------------------

  11:                         5

  12:  

  13: SQL> SELECT TO_CHAR(-4, '0000') FROM DUAL;

  14:  

  15: TO_CH

  16: -----

  17: -0004

So how can we solve this problem? There are currently two methods: one is to use TRIM to remove space, and the other is to use the parameter MI (the MI parameter is a positive number of spaces placed behind the string, place the negative number behind the string). The description of the MI parameter is as follows:

Returns negative value with a trailing minus sign (-).

Returns positive value with a trailing blank.

Restriction: The MI format element can appear only in the last position of a number format model.

Returns the negative and trailing negative signs (-).

Returns a positive value trailing blank.

Restriction: the MI format element can only appear at the last position of the Number Format mode.

In fact, these are only the tip of the iceberg of the TO_CHAR function. A TO_CHAR function is so powerful that it can be seen that ORACLE is profound and profound.

References:

Http://blog.163.com/wangpeng126_2009@126/blog/static/121022611201454111640254/

Https://docs.oracle.com/cd/B19306_01/server.102/b14200/ SQL _elements004.htm#i34570