菜鸟一个,有什么问题,恭候赐教 CREATE FUNCTION ExTract_Int (@strInput varchar(800)) RETURNS int AS BEGIN declare @i char(1),@j int @i=left(@strInput,1) while @i<0 or @i>9 begin @j=len(@strInput)-1 @strInput=right(@strInput,@j) @i=left(@strInput,1) end @i=right(@strInput,1) while @i<0 or @i>9 begin @j=len(@strInput)-1 @strInput=left(@strInput,@j) @i=right(@strInput,1) end return convert(int,@strInput) END
fix itCREATE FUNCTION ExTract_Int (@strInput varchar(800)) RETURNS int AS BEGIN declare @i char(1),@j int select @i=left(@strInput,1) while @i<'0' or @i>'9' begin select @j=len(@strInput)-1 select @strInput=right(@strInput,@j) select @i=left(@strInput,1) end select @i=right(@strInput,1) while @i<'0' or @i>'9' begin select @j=len(@strInput)-1 select @strInput=left(@strInput,@j) select @i=right(@strInput,1) end return @strInput END
哈哈,我自己写了一个字符串到float类型的转换函数,请大家测试测试。 再看看能不能优化。 if Exists(SELECT * FROM sysobjects WHERE NAME = 'str2float' and xtype='FN') begin drop function dbo.str2float select 'drop function getNewClass' end go CREATE FUNCTION dbo.str2float( @str varchar(400) ) --str RETURNS float --返回 分类代码 AS BEGIN
---------------------------------------- if @str is null return(0) if @str = '' return(0) ---------------------------------------- DECLARE @stemp varchar(400) Declare @n int Declare @asc int Declare @asc0 int Declare @asc9 int Declare @ascd int Declare @len int Declare @dot_Flag int -- if have a dot '.' , dot_Flag = 1 ----------------------------------------------- set @asc0 = ascii('0') set @asc9 = ascii('9') set @ascd = ascii('.') set @dot_Flag = 0 -- 0 set @n = 0 -- set @stemp = @str set @len = len(@str) ------------------------------------------------ while @n < @len begin set @asc = ascii(left(@stemp,1)) if @asc = @ascd begin if @dot_Flag = 0 set @dot_Flag = 1 else break end if @asc != @ascd and ( @asc < @asc0 or @asc > @asc9 ) break set @n = @n + 1 set @stemp = right(@str , @len - @n) end
---------------------------------------return if @n = 0 return(0) -------------------------------------- set @stemp = left(@str,@n) --if '.' is the end of string , @stemp+ '0' if ascii(right(@stemp,1)) = @ascd set @stemp = @stemp + '0'
return( cast( @stemp as float) ) END
Declare @p varchar(30) set @p='12345abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0-9]%',@p)-1) as int)
可能不行。 但類似 123.456abcd 之類的可以。 Declare @p varchar(30) set @p='12345abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float)
下面这个不行Declare @p varchar(30) set @p='23123.65.6576wewe.bc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float)
我试了一下两种都可以。 Declare @p varchar(30) set @p='1234.5abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float) select CAST(SUBSTRING(@p,1,PATINDEX('%[^0-9.]%',@p)-1) as float) 结果都是1234.5
不能用cast or convert,会报错“类型错误”,偶已试过了。
CREATE FUNCTION ExTract_Int
(@strInput varchar(800))
RETURNS int AS
BEGIN
declare @i char(1),@j int @i=left(@strInput,1)
while @i<0 or @i>9
begin
@j=len(@strInput)-1
@strInput=right(@strInput,@j)
@i=left(@strInput,1)
end @i=right(@strInput,1)
while @i<0 or @i>9
begin
@j=len(@strInput)-1
@strInput=left(@strInput,@j)
@i=right(@strInput,1)
end
return convert(int,@strInput)
END
(@strInput varchar(800))
RETURNS int AS
BEGIN
declare @i char(1),@j int select @i=left(@strInput,1)
while @i<'0' or @i>'9'
begin
select @j=len(@strInput)-1
select @strInput=right(@strInput,@j)
select @i=left(@strInput,1)
end select @i=right(@strInput,1)
while @i<'0' or @i>'9'
begin
select @j=len(@strInput)-1
select @strInput=left(@strInput,@j)
select @i=right(@strInput,1)
end
return @strInput
END
再看看能不能优化。 if Exists(SELECT * FROM sysobjects WHERE NAME = 'str2float' and xtype='FN')
begin
drop function dbo.str2float
select 'drop function getNewClass'
end
go CREATE FUNCTION dbo.str2float( @str varchar(400) )
--str
RETURNS float
--返回 分类代码
AS
BEGIN
----------------------------------------
if @str is null
return(0)
if @str = ''
return(0)
---------------------------------------- DECLARE @stemp varchar(400)
Declare @n int
Declare @asc int
Declare @asc0 int
Declare @asc9 int
Declare @ascd int
Declare @len int
Declare @dot_Flag int -- if have a dot '.' , dot_Flag = 1
-----------------------------------------------
set @asc0 = ascii('0')
set @asc9 = ascii('9')
set @ascd = ascii('.')
set @dot_Flag = 0 -- 0
set @n = 0 --
set @stemp = @str
set @len = len(@str)
------------------------------------------------
while @n < @len
begin
set @asc = ascii(left(@stemp,1))
if @asc = @ascd
begin
if @dot_Flag = 0
set @dot_Flag = 1
else
break
end
if @asc != @ascd and ( @asc < @asc0 or @asc > @asc9 )
break
set @n = @n + 1
set @stemp = right(@str , @len - @n)
end
---------------------------------------return
if @n = 0
return(0) --------------------------------------
set @stemp = left(@str,@n) --if '.' is the end of string , @stemp+ '0'
if ascii(right(@stemp,1)) = @ascd
set @stemp = @stemp + '0'
return( cast( @stemp as float) )
END
set @p='12345abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0-9]%',@p)-1) as int)
但類似 123.456abcd 之類的可以。
Declare @p varchar(30)
set @p='12345abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float)
set @p='23123.65.6576wewe.bc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float)
Declare @p varchar(30)
set @p='1234.5abc'select CAST(SUBSTRING(@p,1,PATINDEX('%[^0.-9.]%',@p)-1) as float) select CAST(SUBSTRING(@p,1,PATINDEX('%[^0-9.]%',@p)-1) as float) 结果都是1234.5