title: SQL Server unable to trim data of COLLATION type SQL_Latin1_General_CP1_CI_AS
body: <p>We've two database one is the old one which has COLLATION - SQL_Latin1_General_CP1_CI_AS and the new one with COLLATION - Latin1_General_CI_AI (probably the default one).</p>
  
  <p>There's a simple Table1 (ID (int), code(nvarchar(50))) in both the databases. What I'm suppose to do is compare both the tables for its data and find the missing or extra records. </p>
  
  <pre><code>Sample data in old table has code like : 'Code1      '
  Sample data in new table has code like : 'Code1  '
  </code></pre>
  
  <p>What I need to be able to do is compare both the data (from the 'Name' column). I'm unable to trim the data from the old table -</p>
  
  <p><strong>EXAMPLE:</strong></p>
  
  <pre><code>SELECT LTRIM(RTRIM([Name])) from [OLDDB].dbo.Table1
  returns 'Code1     '       -- NOT as expected (probably due to mis-match in charset
  
  SELECT LTRIM(RTRIM([Name])) from [NEWDB].dbo.Table1
  'Code1'                    -- as expected
  </code></pre>
  
  <p>I hope it makes sense. Besides, even if I changes the COLLATION at column level still I was not able to get the ltrim / rtrim work!</p>
  
  <p>Thanks.</p>