Chinaunix首页 | 论坛 | 博客
  • 博客访问: 529018
  • 博文数量: 134
  • 博客积分: 7990
  • 博客等级: 少将
  • 技术积分: 1290
  • 用 户 组: 普通用户
  • 注册时间: 2007-10-29 11:43
文章分类

全部博文(134)

文章存档

2009年(7)

2008年(80)

2007年(47)

我的朋友

分类: Oracle

2008-01-11 19:14:05

今天在DCBA的blog里看到这篇blog: 
 
 昨天要用SQL来获得一个分区的名称, 却发现生成的分区名中总是多了一个空格, 不知道原因何在, 只是数据库的字符集是UTF8. 如下所示:
18:50:50 SQL> SELECT TO_CHAR(9,'09') FROM DUAL;

TO_
---
 09

    用于获得分区名的语句, 其中9是根据一个公式算出来的值.

18:51:23 SQL> SELECT 'P'||TO_CHAR(9,'09') FROM DUAL;

'P'|
----
P 09

    是显示问题? 不是, DUMP函数显示就是多了一个空格.

18:52:45 SQL> SELECT DUMP(TO_CHAR(9,'09')) coldump from dual;

COLDUMP
----------------------------------------
Typ=1 Len=3: 32,48,57

    还有其他人遇到过吗? 帮忙测试一下? 用Perl或Java程序去执行这个查询, 返回值中一样多了个空格.

 

解释:

SQL> SELECT 'P'||TO_CHAR(9,'FM09') FROM DUAL;

'P'|
----
P09

SQL> SELECT DUMP(TO_CHAR(9,'FM09')) coldump from dual;

COLDUMP
--------------------------------------------------------------------------------
Typ=1 Len=2: 48,57


FM

Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function:

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.

In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.

如果你觉得本文不错,可以订阅本站RSS | Google Reader | Bloglines | 抓虾 | 鲜果

阅读(1972) | 评论(0) | 转发(0) |
0

上一篇:有点火

下一篇:linux 中命令yum的用法

给主人留下些什么吧!~~