title: SQL - Conversion Failed When Converting the Varchar Value to Data Type Int
body: <p>I apologize if this seems like an easy question. I am not the best with SQL and I am still learning, but I love to learn new things!</p>
  
  <p>I have a derived varchar column that looks like this when selected from:</p>
  
  <pre><code>|Ref1           |
  |0145972358-0001|
  |5823647892-0002|
  |1697412356-0003|
  |6312548982-0004|
  ----- etc. ------
  </code></pre>
  
  <p>In my query, I am trying to join to another table based on everything to the right of the '-' in Ref1. Since the PK (PackageId) in the other table is a Varchar and does not have leading 0's, I need to trim down the leading 0's or else joining two Varchar datatypes would fail, since '0001' != '1'.</p>
  
  <p>Here is snippets of my attempted queries so far:</p>
  
  <pre><code>SELECT * FROM DatabaseNameOne.SchemaOne.FileDetail fd
  LEFT OUTER join DatabaseNameTwo.SchemaTwo.Packages p
      ON (SUBSTRING(
                       RIGHT(fd.Ref1, CHARINDEX('-', REVERSE(fd.Ref1) - 1)),
                       PATINDEX('%[^0]%', RIGHT(fd.Ref1, CHARINDEX('-', REVERSE(fd.Ref1) - 1))),
                       LEN(RIGHT(fd.Ref1, CHARINDEX('-', REVERSE(fd.Ref1) - 1)))
                   )
         ) = p.PackageId
  </code></pre>
  
  <p>And here is another query I have tried:</p>
  
  <pre><code>SELECT * FROM DatabaseNameOne.SchemaOne.FileDetail fd
  LEFT OUTER JOIN DatabaseNameTwo.SchemaTwo.Packages p
  ON (REPLACE(LTRIM(REPLACE(RIGHT(fd.Ref1, CHARINDEX('-', REVERSE(fd.Ref1) - 1)), '0', '')), '', '0')) = p.PackageId
  </code></pre>
  
  <p>When I run both of these I receive this error: Conversion failed when converting the varchar value to data type int.</p>
  
  <p>I know it is probably something extremely stupid I'm missing, but I've been running into dead ends. Any help on this would be much appreciated! Thanks!</p>