SQL Sever字符型数据转换数字型进行计算

在操作由Excel表格导入SQL Server数据库的字符型数字进行sum（）函数计算时，发生错误。提示为“操作数数据类型 nvarchar 对于 sum 运算符无效。”

可以直观分析是由数字的字符型格式导致的。

我首先想到第一种解决方案利用SQL Server赋值时类型的自动转换，定义数字型变量，将字符型的值付给数字型变量，通过变量的计算求出sum（）后，再次赋值给字符型字段。对于多条数据，需要使用游标进行逐条计算。

我的代码：

 ---修改游标循环计算 累计折旧、净值、净额
declare @ljzj float
declare @jz varchar(255);  
declare @zcbm varchar(255);   
declare @yjtyf float  
declare @bbyz float  
declare @syyx float  	
declare My_Cursor cursor 
 for (select 资产编码,使用月限,已计提月份,本币原值 from GD_XGRQB_N )
 open My_Cursor; 
FETCH NEXT FROM My_Cursor INTO @zcbm,@syyx,@yjtyf,@bbyz;
  WHILE @@FETCH_STATUS = 0
    BEGIN
	select @ljzj=@bbyz*@yjtyf/@syyx;  --求出累计折旧
	select @ljzj=Convert(decimal(18,2),@ljzj)	--精确两位小数
	select @jz=@bbyz-@ljzj;				--求出净值
	update GD_XGRQB_N_20160121 set  累计折旧=@ljzj,净值=@jz,净额=@jz where   资产编码=@zcbm
	FETCH NEXT FROM My_Cursor INTO @zcbm,@syyx,@yjtyf,@bbyz;
    END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO
 

代码执行后，出现了个别大于六位数的数值使用科学计数法表示的情况，例：“1.188e+006”，网络查询结果为float格式导致的结果。

原味转帖：

------------------------------------------------------------------------------------------------------------------------

http://www.cnblogs.com/studyzy/archive/2009/08/27/1555356.html 作者：深蓝

SQL Server中的数值类型分为两种，一种是精确的数值类型，具体的数据类型有：bit、tinyint、smallint、int、bigint、smallmoney、money和decimal，这些数据类型能够精确的表明某以数值；另一种是近似的数值类型，具体就是float和real。浮点数据为近似值，因此，并非数据类型范围内的所有值都能精确地表示。

有些时候我们需要将这些数值类型转换为字符串类型，用到的转换函数就是cast和convert，这两个函数的作用都是进行类型转换，只不过语法格式不同。据说在转换时还是有一定的区别的，不过我个人更习惯于使用convert函数，应该这个函数一方面更像是个函数的语法，另一方面在做时间和数值转换成字符串时还可以指定转换的格式。

对于精确数值的数据类型，转换出来的字符串就是我们存储的数值。比如：

<span style="font-size:18px;">declare @i int
set @i=123456789
print 'test:'+convert(varchar(20),@i)</span>

输出就是：test:123456789
而如果对于近似数值的数据类型，那么可就没有那么简单了。

<span style="font-size:18px;">declare @i float
set @i=123456789
print 'test:'+convert(varchar(20),@i)</span>

输出结果：test:1.23457e+008
输出的结果是使用科学计数法来表示的,再看看可否通过指定转换样式来指定不使用科学计数法呢?帮助文档中说到float 或 real 转换为字符数据时的 style 值:

0（默认值）最大为 6 位数。根据需要使用科学记数法。

1 始终为 8 位值。始终使用科学记数法。

2 始终为 16 位值。始终使用科学记数法。

我们的值是123456789,超过了6位数.所以不管是0还是1,2结果都会使用科学计数法来表示。那么要怎么样才能将我们的数据不转换成科学计数法而输出呢？比较简单的办法就是将近似数据转换为精确数据，然后再将精确数据转换成字符串。

同样以上面的例子为例， 进行两次数据类型的转换如下：

<span style="font-size:18px;">declare @i float
set @i=123456789
print 'test:'+convert(varchar(20),convert(int,@i))</span>

输出：test:123456789
如果是有小数，那么我们要转换出小数位的话，可以转换成decimal，转换出的小数位数在定义decimal时指定。比如要输出4位小数，那么转换代码是：

<span style="font-size:18px;">declare @i float
set @i=123456789.12
print 'test:'+convert(varchar(20),convert(decimal(18,4),@i))</span>

输出：test:123456789.1200

------------------------------------------------------------------------------------------------------------------------

张大拿查看后，建议使用字符转换函数，不使用用float格式，代码更新为

<span style="font-size:18px;"> ---修改游标循环计算 累计折旧、净值、净额
declare @ljzj decimal(18,4)
declare @jz decimal(18,4);  
declare @zcbm varchar(255);   
declare @yjtyf decimal(18,4)
declare @bbyz decimal(18,4)
declare @syyx decimal(18,4)  	
declare My_Cursor cursor  for (select 资产编码,使用月限,已计提月份,本币原值 from GD_XGRQB_N)
open My_Cursor; 
FETCH NEXT FROM My_Cursor INTO @zcbm,@syyx,@yjtyf,@bbyz;
  WHILE @@FETCH_STATUS = 0
    BEGIN
	select @ljzj=@bbyz*@yjtyf/@syyx; <span style="white-space:pre">	</span>        --求出累计折旧
	select @ljzj=CAST(@ljzj AS DECIMAL(18,2))	--精确两位小数
	select @jz=@bbyz-@ljzj;				--求出净值
	update GD_XGRQB_N set  累计折旧=@ljzj,净值=@jz,净额=@jz where 资产编码=@zcbm
	FETCH NEXT FROM My_Cursor INTO @zcbm,@syyx,@yjtyf,@bbyz;
    end
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO
 
 </span>

后，张大拿查看游标循环体后，再次建议不使用游标，直接进行字符型转数值型进行计算，代码为：

<pre name="code" class="sql"><span style="font-size:18px;">update GD_XGRQB_N set 累计折旧 = CONVERT(decimal(18,2),本币原值) * CONVERT(decimal(18,2),已计提月份)/CONVERT(decimal(18,2),使用月限)
update GD_XGRQB_N set 净值 = CONVERT(decimal(18,2),本币原值) -CONVERT(decimal(18,2),累计折旧)
update GD_XGRQB_N set 净额=净值</span>

done！

现整理SQL Server中可以实现字符型数字转换成数值型数字的函数和方法。

1.Convert(int, 字段)  //前一个参数为目标类型，后一个为字段名如果想转换成浮点型/日期 等，可以通过第三个参数来控制格式

convert(int,@i)  --将 @i 变量转化为整型数字类型

同样的，使用
convert(varchar(20),@i) --可以将其他类型的字段转换成字符型

2、

<pre name="code" class="sql">CAST(@i as int)  --将 @i 变量转化为整型数字类型

3、 decimal [ (p[ ,s] )] 和 numeric[ (p[ ,s] )] 

官方注释：
numeric 在功能上等价于 decimal
 
 

  
  

 
 

  
  
   
   p（精度）
  
  
  
  

   
   
最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。 该精度必须是从 1 到最大精度 38 之间的值。 默认精度为 18。
   
   
---即该数值包括整数和小数的总位数，
  
  
  
  
   
   s （小数位数）
  
  
  
  

   
   
小数点右边可以存储的十进制数字的位数。
   
   
一般使用 decimal(18,4),即总共18位，14位整数，4位小数。可以缩写为 dec 和 dec(p、s)。