Microsoft KB Archive/101883

From BetaArchive Wiki

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.