sql如何中科学计数法转换成标准的数值

[小海贼]

我有个字符窜："+1.590759E+01" 想用sql直接转换成标准的数值，请问sql 中有这样的函数吗？
我用这个方法： select cast('+1.590759e+01' as decimal(19,6))，提示：转换出错。

[「已注销」]

SELECT CAST(1.590759E+01 AS DECIMAL(19,6)) RES

RES
---------------------------------------
15.907590

(1 row(s) affected)

[幸运的意外]

可以直接用转换语句来处理就行了：
SELECT CAST(1.590759E+01 AS DECIMAL(18,6))
或者
SELECT CONVERT(DECIMAL(18,6),1.590759E+01)

[fpzgm]

declare @str varchar(50)
select @str='+1.590759E+02'
select CAST(
	   cast(LEFT(@str,CHARINDEX('E',@str)-1) as decimal(19,6))*
	   case when cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)>0 then
       cast(LEFT('100000000000000',cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       else 1.0/cast(LEFT('100000000000000',1+-1*cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       end as decimal(19,6))
--15.907590

select @str='+159.0759E-02'
select CAST(
	   cast(LEFT(@str,CHARINDEX('E',@str)-1) as decimal(19,6))*
	   case when cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)>0 then
       cast(LEFT('100000000000000',cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       else 1.0/cast(LEFT('100000000000000',1+-1*cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       end as decimal(19,6))
--1.590759

[fpzgm]

--建个函数
create function [dbo].[fn_charTodecimal](@str varchar(50))
returns decimal(19,6)
as
begin
return CAST(
	   cast(LEFT(@str,CHARINDEX('E',@str)-1) as decimal(19,6))*
	   case when cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)>0 then
       cast(LEFT('100000000000000',cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       else 1.0/cast(LEFT('100000000000000',1+-1*cast(substring(@str,CHARINDEX('E',@str)+1,3) as int)) as int)
       end as decimal(19,6))
end

--调用
select dbo.fn_charTodecimal('-12.3422E-1')

---1.234220

[hweing]

select cast(cast('+1.590759e+01' as float), decimal(19,6))
不解释，谁用谁知道。

[hweing]

[Quote=引用 5 楼 hweing 的回复:]

select cast(cast('+1.590759e+01' as float), decimal(19,6))
不解释，谁用谁知道。
[/Quote]

肯定有错误，正确的是：
select cast(cast('+1.590759e+01' as float) as decimal(19,6))
笔误，呵呵