使用CONVERT和CAST无法将'10.00000'转为'10'的SQL技术问题

我有一个所有列均设为varchar类型的表，原因是源文件中的列包含多种数据类型，尝试加载为普通表未成功。前6行从文件加载后，我用Column 0的值构建动态SQL传入数据流任务，该动态SQL执行后返回预期的10，但数据流将其追加到表后显示为10.00000，用SSMS查询目标库表也显示10.00000。提取该表到扁平文件时，尝试多种转换方法均无法将'10.00000'转为'10'、'0.00000'转为'0'，扁平文件始终显示原格式。

原查询语句：

SELECT 
      [Column 0]
      ,[Column 1]
      ,[Column 2]
      ,[Column 3]
      ,[Column 4]
      ,[Column 5] --需转为10而非10.00000
      ,[Column 6]  --需转为10而非10.00000
      ,[Column 6]  --需转为0而非0.00000
      ,[Column 7]
      ,[Column 8]
      ,[Column 9]
      ,[Column 10]
      ,[Column 11]
      ,[Column 12]
      ,[Column 13]
      ,[Column 14]
      ,[Column 15]
      ,[Column 16]
      ,[Column 17]
  FROM [Voxware].[dbo].[VoxPackListIn]

尝试过的转换语句：

--CONVERT(varchar(5),CAST([Column 6] AS Integer)) as [Column 6],
--CONVERT(VARCHAR(5),TRUNC(CAST([Column 5] AS NUMERIC)))
--CONVERT(varchar(5),CAST(ABS(QUANTITY) AS Integer)) AS QUANTITY
--CONVERT(VARCHAR(5),(CAST(ROUND([Column 5],0) AS NUMERIC)))
--Cast(QUANTITY AS Integer)

解决方案1：SQL层面使用STR()函数处理 #

STR()函数可直接控制数值的字符串格式，指定小数位数为0即可去掉末尾的.00000：

SELECT 
      [Column 0]
      ,[Column 1]
      ,[Column 2]
      ,[Column 3]
      ,[Column 4]
      ,STR(CAST([Column 5] AS DECIMAL(18,5)), 5, 0) AS [Column 5]
      ,STR(CAST([Column 6] AS DECIMAL(18,5)), 5, 0) AS [Column 6]
      ,STR(CAST([Column 6] AS DECIMAL(18,5)), 5, 0) AS [Column 6_0]
      ,[Column 7]
      ,[Column 8]
      ,[Column 9]
      ,[Column 10]
      ,[Column 11]
      ,[Column 12]
      ,[Column 13]
      ,[Column 14]
      ,[Column 15]
      ,[Column 16]
      ,[Column 17]
  FROM [Voxware].[dbo].[VoxPackListIn]

如果数据无精度问题，也可用FLOAT替代DECIMAL，效率更高：

STR(CAST([Column 5] AS FLOAT), 5, 0) AS [Column 5]

解决方案2：SSIS数据流中用派生列组件转换 #

在数据流任务里添加派生列组件，针对目标列设置以下表达式，先转整数再转回字符串：

• Column 5表达式：(DT_WSTR, 5)(DT_I4)[Column 5]
• Column 6表达式：(DT_WSTR, 5)(DT_I4)[Column 6]
  该转换会自动去掉小数部分，直接生成'10'或'0'格式的字符串。

解决方案3：检查数据流目标表的类型映射 #

确认SSIS数据流中目标表的列映射是否正确：

• 目标列必须设置为DT_STR或DT_WSTR（字符串类型），不能选数值类型
• 如果误选数值类型，即使源是字符串，也会被隐性转换为带小数的数值后再存成字符串，导致出现'10.00000'格式

内容的提问来源于stack exchange，提问作者MaggieW