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

Question

如果这个问题看起来很简单，我很抱歉。我在SQL方面的能力有限，还在学习中，但我喜欢学习新事物！

我有一个派生的varchar列，当从中选择时它看起来像这样：

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

在我的查询中，我正在尝试基于Ref1中“ - ”右侧的所有内容加入到另一个表格。由于另一个表格中的PK（PackageId）是一个Varchar类型，并且没有前导0，因此我需要修剪掉前导0，否则连接两个Varchar数据类型将失败，因为“0001”！=“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

这是我尝试过的另一个查询：

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

当我运行这两个代码时，我收到了以下错误：

Conversion failed when converting the varchar value to data type int.

我知道可能是我遗漏了一些非常愚蠢的东西，但我已经陷入了死胡同。任何关于此问题的帮助将不胜感激！谢谢！

- wibby35

---

SELECT CAST(RIGHT('0145972358-0001', 4) AS INT) 的结果是 1，类似这样吗？ - Jacob H

结束的数字总是4位数吗？ - Greg Viers

Answer 1

这比你想象的要简单得多，只需将其转换为int即可去除零：

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

如果-的位置可能会变化，则可以使用以下方法：

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

否则，您可以简单地执行以下操作：

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

- Aaron Dietz

Answer 2

你提到的愚蠢错误是'-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

我在我的端上尝试了这个，它按照你的期望工作了。

- Sumeet Kale

---

我知道这是某些愚蠢的东西！我在我的端上尝试了一下，也起作用了。感谢你的帮助！ - wibby35

很高兴能够帮助。如果它有效，您可以将其标记为答案。 - Sumeet Kale

Answer 3

如果我们可以假设它总是最后4位数字，那么只需将varchar转换为int，而不是尝试将int转换为varchar（这是导致前导零问题的原因）：

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

- Greg Viers