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

Dear 丶 2022-08-19 15:06 867阅读 0赞

在操作由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][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函数，应该这个函数一方面更像是个函数的语法，另一方面在做时间和数值转换成字符串时还可以指定转换的格式。

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

declare @i int
 set @i=123456789
 print 'test:'+convert(varchar(20),@i)

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

declare @i float
 set @i=123456789
 print 'test:'+convert(varchar(20),@i)

输出结果：test:1.23457e+008  
输出的结果是使用科学计数法来表示的,再看看可否通过指定转换样式来指定不使用科学计数法呢?帮助文档中说到float 或 real 转换为字符数据时的 style 值:  
  
0（默认值）最大为 6 位数。根据需要使用科学记数法。  
  
1 始终为 8 位值。始终使用科学记数法。  
  
2 始终为 16 位值。始终使用科学记数法。  
  
我们的值是123456789,超过了6位数.所以不管是0还是1,2结果都会使用科学计数法来表示。那么要怎么样才能将我们的数据不转换成科学计数法而输出呢？比较简单的办法就是将近似数据转换为精确数据，然后再将精确数据转换成字符串。  
  
同样以上面的例子为例， 进行两次数据类型的转换如下：

declare @i float
 set @i=123456789
 print 'test:'+convert(varchar(20),convert(int,@i))

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

declare @i float
 set @i=123456789.12
 print 'test:'+convert(varchar(20),convert(decimal(18,4),@i))

输出：test:123456789.1200

\------------------------------------------------------------------------------------------------------------------------

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

---修改游标循环计算 累计折旧、净值、净额
 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; 	 --求出累计折旧
 	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

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

<pre name="code" class="sql">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 净额=净值

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)。

[http_www.cnblogs.com_studyzy_archive_2009_08_27_1555356.html]: http://www.cnblogs.com/studyzy/archive/2009/08/27/1555356.html