关于Sybase中char和varchar的空格填充问题

关于Sybase中char型数据

背景

在工作中遇到如下问题：
因为业务要求，在Sybase数据库的view中使用了CAST函数做了数据类型的强制转换（varchar→char）。
使用powercenter从Sybase数据库的view中抽出数据时，发现未强制转换的char类型数据抽出后末尾没有自动填充的半角空格，而CAST后的数据在抽出后填充了半角空格导致与用户需求不同。大致代码如下：

CREATE VIEW VIEW_CUST_COUNTRY_INFO AS
  SELECT 
    CBI.cust_id AS view_cust_id,
    CBI.country_code AS view_country_code,
    CCM.country_name AS view_country_name,
    (CAST
     (CASE CBI.country_code
      WHEN "99" THEN "CHN"
      ELSE CCM.country_code
     )AS CHAR(2)
    )AS view_country_code_res,
    (CAST
     (CASE CBI.country_code
      WHEN null THEN (SELECT country_name FROM COUNTRY_CODE_MST WHERE country_code="99")
      ELSE CCM.country_code)
     )AS CHAR(100)
    )AS view_country_name_res,
  FROM
    CUST_BASIC_INFO CBI
  LEFT JOIN 
    COUNTRY_CODE_MST CCM
  ON CBI.country_code = CCM.country_code
GO

表Country_Code_Mst数据类型如下：

名称	类型	精度
country_code	char	2
country_name	char	100

创建的View_Cust_Country_Info表数据类型如下：

名称	类型	精度
view_country_code	char	2
view_country_name	char	100
view_country_code_res	char	2
view_country_name_res	char	100

具体问题

• 使用DBvisualizer查看view表中数据，可发现项目view_country_name和项目view_country_name_res末尾都自动填充了空格。
• 使用powercenter抽出数据后，发现view_country_name后自动填充的空格消失，而view_country_name_res后空格依旧保留。

问题分析

仔细阅读了sybase关于char与varchar的描述，发现了下述问题：

Only char not null and nchar not null columns are padded to the fullwidth of the column; char null columns are treated like varchar and nchar null columns are treated like nvarchar.

也就是说sybase内部会自动将设置为允许null的char储存为varchar，将设置为not null的char储存为char，并填充满空格。
上述VIEW里面view_country_code和view_country_name是可以被设置为null的，所以在sybase内部是按照varchar储存的（尽管我们定义其为char）；而view_country_name_res因为进行了CASE转换，变为了not null项，所以被填充了空格，导致了式样不一致。

官方例子

下面把sybase用户手册里的Treatment of blanks一节，作为参照例子搬过来。

create table spaces (
    cnot        char(5)     not null, 
    cnull       char(5)     null,  
    vnot        varchar(5)  not null, 
    vnull       varchar(5)  null,  
    explanation varchar(25) not null
    ) 

insert spaces values ("a", "b", "c", "d", "pads char-not-null only") 
insert spaces values ("1    ", "2    ", "3    ", "4    ", "truncates trailing blanks") 
insert spaces values ("    e", "    f", "    g", "    h", "leading blanks, no change") 
insert spaces values ("   w ", "   x ", "   y ", "    z ", "truncates trailing blanks") 
insert spaces values ("", "", "", "", "empty string equals space") 

select "[" + cnot + "]", 
       "[" + cnull + "]", 
       "[" + vnot + "]", 
       "[" + vnull + "]",  
    explanation from spaces 

                                       explanation
 -------  -------  ------- ------- --------------------
 [a    ]  [b]      [c]      [d]      pads char-not-null only 
 [1    ]  [2]      [3]      [4]      truncates trailing blanks
 [    e]  [    f]  [    g]  [    h]  leading blanks, no change
 [   w ]  [   x ]  [   y ]  [   z ]  truncates trailing blanks  
 [ ]      [ ]      [ ]      [ ]      empty string equals space

(5 rows affected)

官方解释：
This example illustrates how the column’s datatype and null type interact to determine how blank spaces are treated:

• Only char not null and nchar not null columns are padded to the full width of the column; char null columns are treated like varchar and nchar null columns are treated like nvarchar.
• Only unichar not null columns are padded to the full width of the column; unichar null columns are treated like univarchar.
• Preceding blanks are not affected.
• Trailing blanks are truncated except for char, unichar, and nchar not null columns.
• The empty string (“ ”) is treated as a single space. In char, nchar, and unichar not null columns, the result is a column-length field of spaces.