日期:2014-05-18  浏览次数:20370 次

★★SQL语句里把科学计数转换成值
比如说 ‘2.0×10^3’ 我怎么把它转换成2000

高手请教 急。。。。


------解决方案--------------------
SQL code

declare @T table (col varchar(9))
insert into @T
select '2.0×10^3' union all
select '3.0×10^4' union all
select '0.2×10^2'

select REPLACE(REPLACE(col,'×','*power('),'^',',')+')' from @T
/*
2.0*power(10,3)
3.0*power(10,4)
0.2*power(10,2)
*/

------解决方案--------------------
参考:
SQL code
create table tb(col nvarchar(30))
insert into tb select '2*5'
insert into tb select '3*8+2*3'
insert into tb select '2*4'
insert into tb select '2*1+3*3+2*3'
go
select col,col as c,0 as s into # from tb
while exists(select 1 from # where charindex('*',c)>0)
begin
update # set s=s+convert(int,left(c,charindex('*',col)-1)),c=convert(varchar(30),right(c+'+',len(c)-charindex('+',c+'+')+1))
end
select col,sum(s)s from # group by col
go
drop table tb,#
/*
col                            s
------------------------------ -----------
2*1+3*3+2*3                    7
2*4                            2
2*5                            2
3*8+2*3                        5

(4 行受影响)
*/

------解决方案--------------------
SQL code

declare @T table (col varchar(9))
insert into @T
select '2.0×10^3' union all
select '3.0×10^4' union all
select '0.2×10^2'

select  
    cast(left(col, charindex('×', col) - 1) as decimal(18, 2)) 
    * power(10,( right(col,len(col)- charindex('^',col)) + 0 )) as col
from @T
/*
col
---------------------------------------
2000.00
30000.00
20.00
*/

------解决方案--------------------
SQL code
declare @v varchar(20)
set @v='2.0×10^3'
select convert(float,LEFT(@v,charindex('×',@v)-1))*power(10,RIGHT(@v,len(@v)-charindex('^',@v)))
/*
----------------------
2000

(1 行受影响)

*/