https://www.cnblogs.com/kerrycode/p/5084411.html
select 'A' || to_char(4,'0000') from dual;
'A'||T
------
A 0004
在官方文档里面你能看到一段介绍
Database SQL Reference
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570
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.
注意红色部分，意思是Number类型转换为字符时，负数会返回前面包含负号的字符串，正数则会返回前面包含空格的字符串，除非格式模式包含MI、S、或PR元素。查看TO_CHAR(4, '0000')返回的字符串长度，你会发现其长度为5.
SELECT LENGTH(TO_CHAR(4, '0000')) FROM DUAL;
LENGTH(TO_CHAR(4,'0000'))
-------------------------
5
SELECT TO_CHAR(-4, '0000') FROM DUAL;
TO_CH
-----
-0004
那么如何解决这个问题呢，目前有两种方法，一种方法是用TRIM去空格，另外一种是使用参数MI(MI参数是正数的空格放到字符串后面，将负数的负号放置在字符串后面)，关于参数MI的解释如下所示：
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.
返回负值与尾随的负号 (-)。
返回正值尾随空白。
限制: MI 格式元素可以只能出现在数字的格式模式的最后一个位置。
select to_char(-4,'0000MI') from dual;
TO_CH
-----
0004-
select trim(to_char(4,'0000')) from dual;
TRIM(
-----
0004
select to_char(4,'0000MI') from dual;
TO_CH
-----
0004 --trailing blank.
select length(to_char(4,'0000MI')) from dual;
LENGTH(TO_CHAR(4,'0000MI'))
---------------------------
5
