为什么 TestID 没有被转换为 INT?

问题描述

开发人员和 DBA，我想我发现了 SQL 2008 R2 中的一个错误，除非你们中的一个能解释下面的内容.你能解释一下为什么在这种情况下 TestID 被转换为 NUMERIC 而不是 INT 吗?请注意，这只是一个示例，向您展示我在尝试更大的问题时遇到的问题，例如自动化脚本，该脚本将从 Excel 或记事本导入的数据转换为 nvarchar/float 到适当的数据类型、int、numeric，日期时间，varchar.

Dev's and DBA's, I think I figured out a bug in SQL 2008 R2, unless one of you can explain the below.Can you please explain why TestID is being converted to NUMERIC instead of INT in this scenario?Please be advised that this is just a sample to show you the problem I came across while trying something bigger such as an automation script that will convert data imported from Excel or Notepad that will be nvarchar/float to the appropriate data types, int, numeric, datetime, varchar.

CREATE TABLE #CorruptData(
TestID VARCHAR(100)
 );

 GO

INSERT INTO #CorruptData
VALUES ('1'),
   ('2');



SELECT
     CASE
        WHEN TestID LIKE '[1-9]%' AND TestID NOT LIKE '0%' THEN CAST(TestID AS INT)
        WHEN TestID LIKE '0%'  THEN CAST(TestID AS NUMERIC(12,2))
   END AS TestID


 INTO #FixedData
 FROM #CorruptData


SELECT  *
FROM    #FixedData

推荐答案

由于 CASE 语句返回类型的方式，您的 1 和 2 已转换为十进制:

Your 1 and 2 got converted to decimal due to the way a CASE statement returns the type:

案例

Result Types 从类型集中返回最高优先级的类型在 result_expressions 和可选的 else_result_expression 中.为了更多信息，请参阅数据类型优先级.

Result Types Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence.

阅读完整文档:http://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx

如果您真的想查看 INT，那么只需将 CASE 语句返回的任何内容转换为 INT:

If you really want to see INT, then just cast whatever is returned from the CASE statement to INT:

CAST(CASE
    WHEN TestID LIKE '[1-9]%' AND TestID NOT LIKE '0%' THEN CAST(TestID AS INT)
    WHEN TestID LIKE '0%'  THEN CAST(TestID AS NUMERIC(12,2))
END AS INT) AS TestID

这篇关于为什么 TestID 没有被转换为 INT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！