create function f_getNum(@var varchar(1000))
returns varchar(1000)
as
begin
declare @ret varchar(1000)
set @ret = ''
while(len(@var)>0)
begin
set @ret = @ret + case when ISNUMERIC(left(@var,1))=1 then left(@var,1) else '' end
set @var = right(@var,len(@var)-1)
end
return @ret
end
select dbo.f_getnum(msginfo) from 表
returns varchar(1000)
as
begin
declare @ret varchar(1000)
set @ret = ''
while(len(@var)>0)
begin
set @ret = @ret + case when ISNUMERIC(left(@var,1))=1 then left(@var,1) else '' end
set @var = right(@var,len(@var)-1)
end
return @ret
end
select dbo.f_getnum(msginfo) from 表
returns varchar(1000)
as
begin
declare @ret varchar(1000)
set @ret = ''
while(len(@var)>0)
begin
set @ret = @ret + case when ISNUMERIC(left(@var,1))=1 and left(@var,1)!='.' then left(@var,1) else '' end
set @var = right(@var,len(@var)-1)
end
return @ret
endselect dbo.f_getNum(msginfo) from 表
returns nvarchar(100)
as
begin
declare @return nvarchar(100)
set @return=''
while patindex('%[0-9]%',@col)>0
begin
set @return=@return+cast(substring(@col,patindex('%[0-9]%',@col),1) as nvarchar(100))
set @col=stuff(@col,patindex('%[0-9]%',@col),1,'')
end
return(@return)
end
go
select dbo.f_getNum('支持072号选手')
---select dbo.f_getNum('msginfo') from 表drop function f_getNum