create function dbo.fn_trimNumber(@var varchar(512))
returns varchar(512) as
begin
return replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(@var
,'0','')
,'1','')
,'2','')
,'3','')
,'4','')
,'5','')
,'6','')
,'7','')
,'8','')
,'9','')
end
go
--> 测试数据: [s]
if object_id('[s]') is not null drop table [s]
create table [s] (name varchar(10))
insert into [s]
select 'aa12bb32' union all
select 'gg00cc3s'
alter function f_s(@name varchar(1000))
returns varchar(1000)
as
begin
declare @i int,@var varchar(1000)
set @i=1
if(patindex('%[0-9]%',@name)<0)
set @var= @name
else
begin
while(@i<=len(@name))
begin
if(patindex('%[0-9]%',substring(@name,@i,1))=0)
begin
set @var=isnull(@var,'')+substring(@name,@i,1)
end
set @i=@i+1
end
end
return @var
end
select dbo.f_s(name) from [s]
