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!

Solution 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

Solution 2

It's much easier than you're thinking, just converting to int removes the zeroes:

SELECT CONVERT(int,'0001') --Outputs 1

This will work if the position of the - can vary:

SELECT CONVERT(INT,RIGHT('0145972358-0001',LEN('0145972358-0001') - CHARINDEX('-','0145972358-0001')))

Otherwise you can simply:

SELECT CONVERT(int,RIGHT('0145972358-0001', 4))

Solution 3

If we can assume it is always the last 4 digits, just cast the varchar into an int, instead of trying to cast the int into a varchar (which is what causes the leading zeros problem):

SELECT * FROM DatabaseNameOne.SchemaOne.FileDetail fd
LEFT OUTER JOIN DatabaseNameTwo.SchemaTwo.Packages p
ON cast(right(fd.Ref1,4) as int) = p.PackageId

Comments

[Jacob H]

SELECT CAST(RIGHT('0145972358-0001', 4) AS INT) result 1 something like this?

[Greg Viers]

Is it always 4 digits at the end?

[wibby35]

I knew it was something stupid! I tried this on my side and it worked as well. Thanks for your help!

[Sumeet Kale]

Happy to help. You can mark it as a answer if its working.