DISTINCT did not count trailing blanks

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  January 25, 2006 at 3:34 pm

  #167276

  Hello,

  I had to write a simple view in SQL Server and create a pivot report from Excel based on this view. One of the grouping fields in Excel Pivot was repeated twice with the value that looked the same, so I suspected there was a difference in spelling in the field values, namely I suspected blanks. I used LEN() function on these similar values and used Distinct too to see the difference in values. LEN function returned the same number for all "suspect" values and DISTINCT returned only one value, so you would think all "suspect" values in the field are the same. But Excel would still give 2 different fields for the values that looked the same. Then I used Right(myfield, 3) function that showed me exactly that one of the values had a trailing blank. Alternatively I used (rtrim(ltrim)) to fix the issue.

  I new about LEN() not counting trailing blanks, but I did not know it about Distinct. BOL does not mention that Unique does not mean that trailing blanks are counted.

  Regards,Yelena Varsha

• Fred Williams-134192

  SSCrazy

  Points: 2482

  More actions

  January 25, 2006 at 4:53 pm

  #617058

  Trailing blanks are ignored in varchar comparisons, and DISTINCT and UNIQUE both follow this behavior:

  create table #junk

  (crap varchar(10) not null)

  insert #junk values ('xxx')

  insert #junk values ('xxx ')

  select distinct crap from #junk

  /*

  crap

  ----------

  xxx

  */

  select * from #junk where crap = 'xxx '

  /*

  crap

  ----------

  xxx

  xxx

  */

  create unique index crapindex on #junk (crap)

  /*

  Server: Msg 1505, Level 16, State 1, Line 1

  CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. Most significant primary key is 'xxx '.

  The statement has been terminated.

  */

  drop table #junk

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  January 26, 2006 at 9:00 am

  #617212

  Fred,

  SQL Server function Right() does account for the trailing blanks. That is how I was able to catch my blank in the string for the first place.

   - Excel function LEN() does account for the trailing blanks

   - SQL Server function LEN() does not account for the trailing blanks.

  I love the consistency. Especially when working with reports.

  Regards,Yelena Varsha