34,592
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_strtonum2]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_strtonum2]
GO
Create Function f_strtonum2 ( @str varchar(200)) Returns numeric(19,3)
WITH ENCRYPTION
AS
begin
declare @len int,@i int,@err int,@temp varchar(200),
@ret numeric(19,3),@tstr varchar(200)
select @len=0,@i=0,@ret=0,@err=0,@temp=''
if rtrim(@str)='' or @str is null
set @err=1
--数字,退出
if isnumeric(@str)=1 and @err=0
select @ret=cast(@str as numeric(19,3)),@err=1
if @err=0
begin
select @str=rtrim(@str),@len=len(rtrim(@str))
--
if isnumeric(left(@str,1))=1 and @err=0 and left(@str,1) not in('.',',','+','-')
begin
while @i<=@len
begin
select @i=@i+1,@tstr=left(@str,@i)
if isnumeric(@tstr)=0
begin
select @ret=cast(left(@tstr,@i-1) as numeric(19,3))
break
end
else
continue
end
set @err=1
end
end
return @ret
end
go
[/code]
create function f_str(@str varchar(400))
returns numeric(12,2)
as
begin
while patindex('%[^0-9]%',@str)>0
begin
set @str = stuff( @str, patindex('%[^0-9]%',@str) ,1 ,'')
end
return cast(@str as numeric(12,2))
end
go
select dbo.f_str('saf123asd234')
drop function f_str
/*
--------------
123234.00
(所影响的行数为 1 行)
*/
--考虑到四楼说的情况,将函数完善一下(主要包括多符号时,以及符号在数字后面等情况。)
create function GetNumbers(@var varchar(50))
returns numeric(12,3)
as
begin
declare @num varchar(50)
set @num=''
if(patindex('%[0-9]%',@var)=0)
set @num='0'
else
begin
while(patindex('%[0-9.+-]%',@var)>0)
begin
if(substring(@var,patindex('%[0-9.+-]%',@var),1) in (',','+','-') and (patindex('%[.+-]%',@num)>0 or patindex('%[0-9]%',@num)>0))
begin
set @var=stuff(@var,patindex('%[0-9.+-]%',@var),1,'')
continue;
end
else
begin
set @num=@num+substring(@var,patindex('%[0-9.+-]%',@var),1)
set @var=stuff(@var,patindex('%[0-9.+-]%',@var),1,'')
end
end
end
return cast(@num as numeric(12,3))
end
--测试
select dbo.GetNumbers('+')
select dbo.GetNumbers(',')
select dbo.GetNumbers('.')
select dbo.GetNumbers('-')
select dbo.GetNumbers('-123.2cbm')
select dbo.GetNumbers('+123.2cbm')
select dbo.GetNumbers('+123sas3.2cbm')
select dbo.GetNumbers('123sas3+.2cbm')
select dbo.GetNumbers('+123s-as3.+2cbm')
+ 出错 0.000
, 出错 0.000
. 出错 0.000
- 出错 0.000
,, 出错 0.000
-123.2cbm 0.000 -123.200
+123.2cbm 0.000 123.200
create function GetNumbers(@var varchar(50))
returns numeric(12,3)
as
begin
declare @num varchar(50)
set @num=''
if(patindex('%[0-9]%',@var)=0)
set @num='0'
else
begin
while(patindex('%[0-9]%',@var)>0)
begin
set @num=@num+substring(@var,patindex('%[0-9.+-]%',@var),1)
set @var=stuff(@var,patindex('%[0-9.+-]%',@var),1,'')
end
end
return cast(@num as numeric(12,3))
end
go
--测试
select dbo.GetNumbers('+')
select dbo.GetNumbers(',')
select dbo.GetNumbers('.')
select dbo.GetNumbers('-')
select dbo.GetNumbers('-123.2cbm')
select dbo.GetNumbers('+123.2cbm')
select dbo.GetNumbers('+123sas3.2cbm')