用函数去掉数字:if object_id('F_str') is not null drop function F_str go create function F_str(@Str nvarchar(1000)) returns nvarchar(1000) as begin while patindex('%[0-9]%',@Str)>0 set @Str=stuff(@Str,patindex('%[0-9]%',@Str),1,'') return @Str end go select dbo.F_str('a1b2c3')
或只保留字母if object_id('F_str') is not null drop function F_str go create function F_str(@Str nvarchar(1000)) returns nvarchar(1000) as begin while patindex('%[^a-z]%',@Str)>0 set @Str=stuff(@Str,patindex('%[^a-z]%',@Str),1,'') return @Str end go select dbo.F_str('a1b2c3')
create function replace_str(@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.replace_str('a1b2c3')
declare @str nvarchar(50),@i int,@num varchar(10),@Result varchar(10) set @i=1 set @Result='' set @str='a1b2c3' while @i<len(@str)+1 begin set @num=substring(@str,@i,1) if @num not like '[0-9]' begin set @Result=@Result+@num end set @i=@i+1 end if @Result='' set @Result='0' select @Result as result
drop function F_str
go
create function F_str(@Str nvarchar(1000))
returns nvarchar(1000)
as
begin
while patindex('%[0-9]%',@Str)>0
set @Str=stuff(@Str,patindex('%[0-9]%',@Str),1,'')
return @Str
end
go
select dbo.F_str('a1b2c3')
drop function F_str
go
create function F_str(@Str nvarchar(1000))
returns nvarchar(1000)
as
begin
while patindex('%[^a-z]%',@Str)>0
set @Str=stuff(@Str,patindex('%[^a-z]%',@Str),1,'')
return @Str
end
go
select dbo.F_str('a1b2c3')
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.replace_str('a1b2c3')
set @i=1
set @Result=''
set @str='a1b2c3' while @i<len(@str)+1
begin
set @num=substring(@str,@i,1)
if @num not like '[0-9]'
begin
set @Result=@Result+@num
end
set @i=@i+1
end
if @Result='' set @Result='0'
select @Result as result