转换数据类型 varchar 时出错 [英] Error converting data type varchar

问题描述

我目前有一个表，其中一列是 varchar.此列可以包含数字或文本.在某些查询期间，我将其视为 bigint 列(我在它和另一个表中的列之间进行了连接，该列是 bigint)

I currently have a table with a column as varchar. This column can hold numbers or text. During certain queries I treat it as a bigint column (I do a join between it and a column in another table that is bigint)

只要该字段中只有数字就没有问题，但即使该字段中只有一行有文本而不是数字，我也会收到将数据类型 varchar 转换为 时出错"bigint."错误，即使在 WHERE 部分我确保没有出现任何文本字段.

As long as there were only numbers in this field had no trouble but the minute even one row had text and not numbers in this field I got a "Error converting data type varchar to bigint." error even if in the WHERE part I made sure none of the text fields came up.

为了解决这个问题，我创建了一个如下的视图:

To solve this I created a view as follows:

SELECT     TOP (100) PERCENT ID, CAST(MyCol AS bigint) AS MyCol
FROM         MyTable
WHERE     (isnumeric(MyCol) = 1)

但即使视图仅显示具有数值的行并将 Mycol 强制转换为 bigint，我在运行以下查询时仍然遇到将数据类型 varchar 转换为 bigint 的错误:

But even though the view shows only the rows with numeric values and casts Mycol to bigint I still get a Error converting data type varchar to bigint when running the following query:

SELECT * FROM MyView where mycol=1

当对视图进行查询时，它不应该知道它背后发生了什么！它应该只看到两个 bigint 字段！(见附图，即使是mssql管理工作室也将视图字段显示为bigint)

When doing queries against the view it shouldn't know what is going on behind it! it should simply see two bigint fields! (see attached image, even mssql management studio shows the view fields as being bigint)

推荐答案

好的.我终于创建了一个有效的视图:

OK. I finally created a view that works:

SELECT TOP (100) PERCENT id, CAST(CASE WHEN IsNumeric(MyCol) = 1 THEN MyCol ELSE NULL END AS bigint) AS MyCol
FROM         dbo.MyTable
WHERE     (MyCol NOT LIKE '%[^0-9]%')

感谢 AdaTheDev 和 CodeByMoonlight.我用你的两个答案来解决这个问题.(当然也感谢其他回复者)

Thanks to AdaTheDev and CodeByMoonlight. I used your two answers to get to this. (Thanks to the other repliers too of course)

现在，当我加入其他 bigint cols 或执行诸如SELECT * FROM MyView where mycol=1"之类的操作时，它会返回正确的结果，没有错误.我的猜测是查询本身中的 CAST 会导致查询优化器不查看原始表，正如 Christian Hayter 所说的可能正在处理其他视图

Now when I do joins with other bigint cols or do something like 'SELECT * FROM MyView where mycol=1' it returns the correct result with no errors. My guess is that the CAST in the query itself causes the query optimizer to not look at the original table as Christian Hayter said may be going on with the other views

这篇关于转换数据类型 varchar 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！