★SQL语句里把科学计数转换成值,该如何解决

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

高手请教 急。。。。


[解决办法]

SQL code

declare @T table (col varchar(9))insert into @Tselect '2.0×10^3' union allselect '3.0×10^4' union allselect '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'goselect col,col as c,0 as s into # from tbwhile exists(select 1 from # where charindex('*',c)>0)beginupdate # set s=s+convert(int,left(c,charindex('*',col)-1)),c=convert(varchar(30),right(c+'+',len(c)-charindex('+',c+'+')+1))endselect col,sum(s)s from # group by colgodrop table tb,#/*col                            s------------------------------ -----------2*1+3*3+2*3                    72*4                            22*5                            23*8+2*3                        5(4 行受影响)*/
[解决办法]
SQL code
declare @T table (col varchar(9))insert into @Tselect '2.0×10^3' union allselect '3.0×10^4' union allselect '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 colfrom @T/*col---------------------------------------2000.0030000.0020.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 行受影响)*/