当将Varchar值转换为数据类型Int时，SQL -转换失败

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!

如果这个问题看起来很简单，我很抱歉。我不是SQL最好的，我还在学习，但是我喜欢学习新东西!

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

我有一个派生的varchar列，从:

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

在我的查询中，我尝试基于Ref1中“-”右边的所有内容连接到另一个表。由于另一个表中的PK (PackageId)是一个Varchar，并且没有前导0，我需要减少前导0，否则加入两个Varchar数据类型会失败，因为'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.

当我运行这两个时，我收到了这个错误:转换失败时，将varchar值转换为数据类型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!

我知道我可能错过了一些极其愚蠢的事情，但我已经陷入了死胡同。如有任何帮助，我们将不胜感激!谢谢!

3 个解决方案

#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

你刚才说的那个愚蠢的错误是你的“-1”的位置，应该是在那个结束括号之后。它应该是这样的

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

我在我这边试过了，果然不出所料。

#2

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):

如果我们可以假设它总是最后4位数字，那么只需将varchar转换为int，而不是试图将int转换为varchar(这就是导致前导0问题的原因):

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

#3

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

它比你想象的要简单得多，只要转换成int就可以去掉0:

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))

#1