create function getnumber (@s varchar(1000)) returns numeric(22,4) as begin declare @r varchar(30) set @r='' declare @i int set @i=1 declare @c varchar(10) set @c=substring(@s,@i,1) declare @b bit set @b=0 while @i<len(@s) begin if @c in ('0','1','2','3','4','5','6','7','8','9','.') begin set @r=@r+@c set @b=1 end else if @b=1 goto Label set @i=@i+1 set @c=substring(@s,@i,1) end Label: if @r='' return 0 return cast(@r as numeric(22,4)) end调用: select dbo.getnumber('预付209.88元') or select dbo.getnumber(字符字段) as 别名,... from 表名
create function getnumber (@s varchar(1000))
returns numeric(22,4)
as
begin
declare @r varchar(30)
set @r=''
declare @i int
set @i=1
declare @c varchar(10)
set @c=substring(@s,@i,1)
declare @b bit
set @b=0
while @i<len(@s)
begin
if @c in ('0','1','2','3','4','5','6','7','8','9','.')
begin
set @r=@r+@c
set @b=1
end
else
if @b=1 goto Label
set @i=@i+1
set @c=substring(@s,@i,1)
end
Label:
if @r='' return 0
return cast(@r as numeric(22,4))
end调用:
select dbo.getnumber('预付209.88元')
or
select dbo.getnumber(字符字段) as 别名,... from 表名