SQL Server 2008:varchar转换为数值数据溢出,可能是因为有些是范围

您的数据库显然是某些调查的结果,并且似乎包含原始调查数据。通常的方法是通过ECCD(提取、清理、符合、交付)流程运行此过程,并将干净和标准化的数据存储到单独的数据库(可能是仓库)中,然后将该数据库用于分析和报告。

如果有SSIS,请使用数据分析任务了解其中包含的字符串类型。这个 列模式配置文件 在字符串列上报告一组正则表达式,因此您将了解这些字符串中的内容。如果没有SSIS,可以使用 eObjects数据清理器 做同样的事。

如果您不能保留一个新的数据库或至少一个新的表——至少向这个表添加一个数值列,然后从这些字符串中提取数值到新的列中。您可能需要使用“其他东西”(ssis、pentaho kettle、python、vb、c)来完成这项工作——一般来说,t-sql在字符串处理方面不太擅长。

我的猜测是,这不是唯一一个内部有垃圾的列,因此任何运行在此列上的分析都可能是无用的。

如果您仍然认为范围是唯一的问题,这个例子可能有帮助:

首先是一些数据

DECLARE @myTable TABLE ( 
  AlUnits varchar(10)
  ) ;

INSERT  INTO @myTable
        (AlUnits )
VALUES  ( '10' )
,       ( '15' )
,       ( '20' )
,       ( '7 - 12' )
,       ( '3 - 5' )
;

查询将记录分为两组,数字和非数字——假定范围。

;
WITH  is_num
        AS ( SELECT CAST(AlUnits AS decimal(6, 2)) AS Units_LO
                   ,CAST(AlUnits AS decimal(6, 2)) AS Units_HI
             FROM   @myTable
             WHERE  ISNUMERIC(AlUnits) = 1
           ),
      is_not_num
        AS ( SELECT CAST( RTRIM(LTRIM(LEFT(AlUnits,
                          CHARINDEX('-', AlUnits) - 1)))
                        AS decimal(6,2)) AS Units_LO
                   ,CAST(RTRIM(LTRIM(RIGHT(AlUnits,
                               LEN(AlUnits)
                               - CHARINDEX('-', AlUnits))))
                        AS decimal(6,2)) AS Units_HI
             FROM   @myTable
             WHERE  ISNUMERIC(AlUnits) = 0
           )
  SELECT  Units_LO
         ,Units_HI
         ,CAST(( Units_LO + Units_HI ) / 2.0 AS decimal(6, 2)) AS Units_Avg
  FROM    is_num
  UNION ALL
  SELECT  Units_LO
         ,Units_HI
         ,CAST(( Units_LO + Units_HI ) / 2.0 AS decimal(6, 2)) AS Units_Avg
  FROM    is_not_num ;

返回:

Units_LO    Units_HI    Units_Avg 
----------- ----------- ----------
10.00       10.00       10.00     
15.00       15.00       15.00     
20.00       20.00       20.00     
7.00        12.00       9.50      
3.00        5.00        4.00      

不确定简单的方法。

一个正确的方法是将数字存储在两列中,分别是酒精每工作分钟和酒精每工作最大值。

正如您所说,您需要计算数值,然后可以在查询中使用这些数值。

可能最简单的方法是使用一些简单的逻辑,使用字符串函数和字符串到数值函数计算平均值或上限。

如果你想要的只是上限,只需在“-”后面加上字符就可以了。

“可能是因为有些是范围”-您知道“范围”不是SQL Server数据类型吗?你有非数字数据,你试图转换成数字数据,你有一个标量值,你比较的是一个非标量值。

此数据库有一些问题。