FIX: String Functions for Char Datatype in a View
  
PSS ID Number: Q101883
Article last modified on 10-31-1994
 
4.20
 
OS/2
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2 for OS/2
----------------------------------------------------------------------
 
BUG# OS/2: 1734 (4.2)
 
SYMPTOMS
========
 
Using string functions RTRIM, LOWER, UPPER, LTRIM, and Reverse on a
char datatype column of a View returns the result as padded with
blanks to be 256 character in length. Datalength function does return
correct length. This does not happen for varchar datatype column in
the view.
 
Using the base table instead of the view also returns correct results.
 
For example, in the following script, function RTRIM returns results
padded with blanks to be 256 characters in length.
 
   create table T1 (textdat char(30))
   go
   insert into T1 (textdat) values ('a')
   insert into T1 (textdat) values ('ab')
   go
   create view VT1 (textdat) as select textdat from T1
   go
   select rtrim(textdat) from VT1 /* incorrect */
   go
 
WORKAROUND
==========
 
Note that the problem does not occur on the varchar datatype columns
in a view. Therefore, you may modify your view creation statement as:
 
   create view VT1 (textdat) as
      select convert(varchar(30),textdat) from T1
 
This causes the results to be correctly returned for the string
functions. Alternately, you can also convert the char value to a
varchar before applying the rtrim or other string functions; for
example:
 
   select rtrim(convert(varchar(30),textdat)) from VT1
 
STATUS
======
 
Microsoft has confirmed this to be a problem in SQL Server version
4.2 for OS/2. This problem was corrected in SQL Server version
4.2aK11. For more information, contact your primary support provider.
 
Additional reference words: 4.20 upper lower
KBCategory: kbprg
KBSubcategory: SSrvServer
 
=============================================================================
 
Copyright Microsoft Corporation 1994.
