[20161003]to_char与前导空格.txt

[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.

--下次在测试中注意这个细节。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2127576/，如需转载，请注明出处，否则将追究法律责任。