SQL - Conversion Failed When Converting the Varchar Value to Data Type Int

Question

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!

I have a derived varchar column that looks like this when selected from:

|Ref1           |
|0145972358-0001|
|5823647892-0002|
|1697412356-0003|
|6312548982-0004|
----- etc. ------

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'.

Here is snippets of my attempted queries so far:

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

And here is another query I have tried:

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

When I run both of these I receive this error: Conversion failed when converting the varchar value to data type int.

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!

Answer 1

The stupid mistake you talked about was the position of your '-1', it should have been after that closing bracket. So it should look like

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

I tried this on my side and it worked as you expected