[20161003]to_char与前导空格.txt
--昨天做测试时遇到的问题：
create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--但是我写成如下：
create table t1 tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
--我以为两者应该占用大小一样，但是发现不同。
SCOTT@book> @ &r/desc t1
Name Null? Type
------- -------- --------------
1 ID NUMBER
2 NAME VARCHAR2(33)
--发现name类型是VARCHAR2(33),根本不是我期望的VARCHAR2(32).
SCOTT@book> column c120 format a120
SCOTT@book> select dump(name) c120 from t1 where rownum=1 ;
C120
------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=33: 32,48,48,48,48,48,49,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65,65
--自己发现前面有一个空格。
在官方文档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.
--使用FM正常，什么意思呢？
SYS@book> SELECT TO_CHAR(12, 'FM0000') FROM DUAL;
TO_CH
-----
0012
SYS@book> SELECT length(TO_CHAR(12, 'FM0000')) FROM DUAL;
LENGTH(TO_CHAR(4,'FM0000'))
---------------------------
4
FM Yes
Returns a value with no leading or trailing blanks.
See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference.
--下次在测试中注意这个细节。