1. 基础使用
declare @Name nvarchar(50)
set @Name='我正在123学.习22.SQL中11.的一些函数'
print patindex('%在%',@Name)
print patindex('%[0-9]%',@Name)
print stuff(@Name,1,1,'被插')
print stuff(@Name,3,4,'被插')
while patindex('%[^0-9]%',@Name)>0
begin
set @Name=stuff(@Name,patindex('%[^0-9]%',@Name),1,'')
end
print @Name
2. 封装方法
create function dbo.GetNoFromStr(@str nvarchar(100))
returns bigint
as
begin
while patindex('%[^0-9]%',@str)>0
begin
set @str=stuff(@str,patindex('%[^0-9]%',@str),1,'')
end
return convert(bigint,@str)
end
3. 调用方式
select dbo.GetNoFromStr('25号') as result