SQL 转换数据类型 varchar 时出错

Error converting data type varchar

提问by adinas

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 bigintcolumn (I do a join between it and a column in another table that is bigint)

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

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 varcharto bigint." error even if in the WHERE part I made sure none of the text fields came up.

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

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)

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 bigintwhen running the following query:

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

SELECT * FROM MyView where mycol=1

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)

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

采纳答案by adinas

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]%')

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

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

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

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

回答by AdaTheDev

Ideally, you want to try to avoid storing the data in this form - would be worth splitting the BIGINT data out in to a separate column for both performance and ease of querying.

理想情况下，您希望尽量避免以这种形式存储数据 - 值得将 BIGINT 数据拆分到单独的列中，以提高性能和便于查询。

However, you can do a JOIN like this example. Note, I'm not using ISNUMERIC()to determine if it's a valid BIGINT because that would validate incorrect values which would cause a conversion error (e.g. decimal numbers).

但是，您可以像此示例一样执行 JOIN。请注意，我没有使用 ISNUMERIC()来确定它是否是有效的 BIGINT，因为这会验证会导致转换错误（例如十进制数）的不正确值。

DECLARE @MyTable TABLE (MyCol VARCHAR(20))
DECLARE @OtherTable TABLE (Id BIGINT)

INSERT @MyTable VALUES ('1')
INSERT @MyTable VALUES ('Text')
INSERT @MyTable VALUES ('1 and some text')
INSERT @MyTable VALUES ('1.34')
INSERT @MyTable VALUES ('2')
INSERT @OtherTable VALUES (1)
INSERT @OtherTable VALUES (2)
INSERT @OtherTable VALUES (3)

SELECT *
FROM @MyTable m
    JOIN @OtherTable o ON CAST(m.MyCol AS BIGINT) = o.Id
WHERE m.MyCol NOT LIKE '%[^0-9]%'

Update:The only way I can find to get it to work for having a WHERE clause for a specific integer value without doing another CAST() on the supposedly bigint column in the where clause too, is to use a user defined function:

更新：我能找到的唯一方法是使用用户定义的函数，而不是在 where 子句中所谓的 bigint 列上执行另一个 CAST() 子句，而是使用特定整数值的 WHERE 子句：

CREATE  FUNCTION [dbo].[fnBigIntRecordsOnly]()
RETURNS @Results TABLE (BigIntCol BIGINT)
AS
BEGIN
INSERT @Results
SELECT CAST(MyCol AS BIGINT)
FROM MyTable
WHERE MyCol NOT LIKE '%[^0-9]%'
RETURN
END

SELECT * FROM [dbo].[fnBigIntRecordsOnly]() WHERE BigIntCol = 1

I don't really think this is a great idea performance wise, but it's a solution

我真的不认为这是一个很好的表现明智的想法，但它是一个解决方案

回答by Christian Hayter

To answer your question about the error message: when you reference a view name in another query (assuming it's a traditional view not a materialised view), SQL Server effectively does a macro replacement of the view definition into the consuming query and then executes that.

回答您关于错误消息的问题：当您在另一个查询中引用视图名称时（假设它是传统视图而不是物化视图），SQL Server 有效地将视图定义宏替换到消费查询中，然后执行它。

The advantage of doing this is that the query optimiser can do a much better job if it sees the whole query, rather than optimising the view separately as a "black box".

这样做的好处是，如果查询优化器看到整个查询，它可以做得更好，而不是将视图单独优化为“黑匣子”。

A consequence is that if an error occurs, error descriptions may look confusing because the execution engine is accessing the underlying tables for the data, not the view.

结果是，如果发生错误，错误描述可能看起来很混乱，因为执行引擎正在访问数据的基础表，而不是视图。

I'm not sure how materialised views are treated, but I would imagine that they are treated like tables, since the view data is cached in the database.

我不确定物化视图是如何处理的，但我认为它们被视为表，因为视图数据缓存在数据库中。

Having said that, I agree with previous answers - you should re-think your table design and separate out the text and integer data values into separate columns.

话虽如此，我同意以前的答案 - 您应该重新考虑您的表格设计并将文本和整数数据值分离到单独的列中。

回答by MartW

Try changing your view to this :

尝试将您的观点更改为：

SELECT TOP 100 PERCENT ID, 
Cast(Case When IsNumeric(MyCol) = 1 Then MyCol Else null End AS bigint) AS MyCol
FROM MyTable
WHERE (IsNumeric(MyCol) = 1)

回答by Gertjan

Try using this:

尝试使用这个：

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

This should work since the inner select only return numeric values and the outer select can therefore convert all values from the first select into a numeric. It seems that in your own code SQL tries to cast before executing the isnumeric function (maybe it has something to do with optimizing).

这应该有效，因为内部选择仅返回数字值，因此外部选择可以将第一个选择中的所有值转换为数字。似乎在您自己的代码中，SQL 在执行 isnumeric 函数之前尝试进行强制转换（可能与优化有关）。

回答by Regent

Have you tried to convert other table's bigint field into varchar? As for me it makes sense to perform more robust conversion... It shouldn't affect your performance too much if varchar field is indexed.

您是否尝试将其他表的 bigint 字段转换为 varchar？对我来说，执行更强大的转换是有意义的......如果对 varchar 字段进行索引，它应该不会对你的性能产生太大影响。

回答by Jan B. Kjeldsen

Consider creating a redundant bigint field to hold the integer value of af MyCol.

考虑创建一个冗余的 bigint 字段来保存 af MyCol 的整数值。

You may then index the new field to speed up the join.

然后，您可以索引新字段以加快连接速度。

回答by Shiraz Bhaiji

Try doing the select in 2 stages.

尝试分 2 个阶段进行选择。

first create a view that selects all columns where my col is nummeric.

首先创建一个视图，该视图选择我的 col 为数字的所有列。

Then do a select in that view where you cast the varchar field.

然后在您投射 varchar 字段的那个视图中做一个选择。

The other thing you could look at is your design of tables to remove the need for the cast.

您可以查看的另一件事是您的表格设计，以消除对演员表的需要。

EDIT

编辑

• Are some of the numbers larger than bigint?
• Are there any spaces, leading, trailing or in the number?
• Are there any format characters? Decimal points?

• 有些数字比 bigint 大吗？
• 是否有任何空格，前导，尾随或数字？
• 有没有格式字符？小数点？