title: SQL JOIN using Concatenated Field
body: <p>I have two tables, Table1 contains a column with what constitutes a partial value of a column in Table2, for example:</p>
  
  <blockquote>
    <p>Table1.XName = "123456"
    Table2.ZName = "ABC-123456"</p>
  </blockquote>
  
  <p>I need to create a JOIN that matches these up, but with MS-SQL 2008 I'm having trouble making this work.  Here's a sample of my attempt:</p>
  
  <blockquote>
    <p>SELECT * FROM Table1
    LEFT OUTER JOIN Table2 ON ('ABC-'+Table1.XName)=Table2.ZName</p>
  </blockquote>
  
  <p>It doesn't matter what type of JOIN I use, or in which direction either, it fails.  I know I'm doing something boneheaded here so if anyone can point me in the right direction I would appreciate it.</p>