SQL 分数转小数

转自：http://blog.163.com/yuanchunyin@126/blog/static/55384535200911443433323/

 

数据库表BOM，字段totalquantity，varchar类型，分数表示用量

select totalquantity,round(cast(left(totalquantity,charindex('/',totalquantity)-1) as float)/cast(substring(totalquantity,charindex('/',totalquantity)+1,20) as float)+0.00004,4)
from bom where totalquantity like '%/%'

Charindex('/',totalquantity)    查找分号‘/’的位置

left(totalquantity,charindex('/',totalquantity)-1)   取出分子数

cast(left(totalquantity,charindex('/',totalquantity)-1) as float)   把分子转换成float类型

substring(totalquantity,charindex('/',totalquantity)+1,20)   取出分母数

cast(substring(totalquantity,charindex('/',totalquantity)+1,20) as float)  分母转换为float类型

round(cast(left(totalquantity,charindex('/',totalquantity)-1) as float)/cast(substring(totalquantity,charindex('/',totalquantity)+1,20) as float)+0.00004,4) 全进位保留4位小数

下面为更新为小数语句

update bom set  totalquantity=round(cast(left(totalquantity,charindex('/',totalquantity)-1) as float)/cast(substring(totalquantity,charindex('/',totalquantity)+1,20) as float)+0.00004,4)  where totalquantity like '%/%'

 

 

 

 

declare @a varchar(10)
set @a='1/2'
select round(cast(left(@a,charindex('/',@a)-1) as float)/cast(substring(@a,charindex('/',@a)+1,20) as float)+0.00004,4)