why does upper(varchar(n)) yield varchar(2n) ?

• Frédéric van der Plancke

  SSC Enthusiast

  Points: 198

  More actions

  April 3, 2008 at 6:53 am

  #71301

  Hello,

  I'm creating a view based on a table, with a field Lang char(2), that I want to Upper. The results is, oddly, of length 4 (type varchar(4)) and the upper function is the culprit, as shown by experiment. It seems the result of upper applied to something of type varchar(n) is consistently of type varchar(2n).

  Why is that ? Can I safely cast the result back to varchar(n) or char(n), perhaps assuming the fields only contains ASCII chars ? (This factor 2 makes me thing there is some Unicode conversion going on somewhere.)

  Here is a script to reproduce the problem:

  CREATE TABLE [PERSON_LANG] (

  [PersonID] [int] NOT NULL ,

  [Lang] [char] (2) COLLATE French_CI_AS NOT NULL

  ) ON [PRIMARY]

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  CREATE VIEW dbo.V_PERSONUPPERLANG

  AS

  SELECT PersonID, UPPER(Lang) AS PersonLang

  FROM dbo.PERSON_LANG

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  Inspection of V_PERSONUPPERLANG through the SQL Query analyser object browser shows PersonLang to be varchar(4).

• Frédéric van der Plancke

  SSC Enthusiast

  Points: 198

  More actions

  April 3, 2008 at 7:04 am

  #797773

  The problem does not happen on MSSQL 2005 (there, PersonLang is varchar(2) as expected) so I'll assume this is a SQL Server 2000 bug...