declare @str varchar(20) set @str='a1b23c456' while patindex('%[0-9]%', @str)>0 select @str=stuff(@str, patindex('%[0-9]%', @str), 1, '*')select @str--result -------------------- a*b**c***(1 row(s) affected)
如果在记录集中,那不麻烦吗? 没有像JavaScript 中的正则表达吗?
这样吧 drop table #t2 create table #t2 (aa varchar(20)) insert #t2 select '刘德华123发文' union all select '刘德华收文' union all select '张123学友发文' union all select '张学友收文' union all select '刘德华信件' union all select '张学423友信笺' select * from #t2 update #t2 set aa=substring(aa,1,PATINDEX('%[0-9]%',aa)-1)+reverse(substring(reverse(aa),1,PATINDEX('%[0-9]%', reverse(aa))-1)) where PATINDEX('%[0-9]%', aa)>0
select Replace('aweiufeiowuf1kjiyh5i45nininin3',,'*') declare @a varchar(8000) declare @b int declare @c int declare @d int set @a='faeiureianen1ilasudfiu845isaeji74ir0' set @b=0 while (@b<=9) begin set @a=replace(@a,cast(@b as varchar(1)),'*') set @b=@b+1 endselect @a
create function abc(@a varchar(8000)) returns varchar(8000) as begin declare @b int declare @c int declare @d int set @b=0 while (@b<=9) begin set @a=replace(@a,cast(@b as varchar(1)),'*') set @b=@b+1 end return @a endselect abc(字段) from t
--正则表达式函数 CREATE function dbo.regexReplace (@source varchar(5000), --原字符串 @regexp varchar(1000), --正则表达式 @replace varchar(1000), --替换值 @globalReplace bit = 0, --是否是全局替换 @ignoreCase bit = 0 --是否忽略大小写 ) returnS varchar(1000) AS begin declare @hr integer declare @objRegExp integer declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OADestroy @objRegExp IF @hr <> 0 begin return null endreturn @result endselect aa='***123456**65658**77' into #a select * from #a select dbo.regexReplace(aa,'[*]','',1,1) from #a drop table #a
declare @str varchar(20)
set @str='a1b23c456'
while patindex('%[0-9]%', @str)>0
select @str=stuff(@str, patindex('%[0-9]%', @str), 1, '*')select @str--result
--------------------
a*b**c***(1 row(s) affected)
没有像JavaScript 中的正则表达吗?
drop table #t2
create table #t2 (aa varchar(20))
insert #t2
select '刘德华123发文' union all
select '刘德华收文' union all
select '张123学友发文' union all
select '张学友收文' union all
select '刘德华信件' union all
select '张学423友信笺' select * from #t2 update #t2
set aa=substring(aa,1,PATINDEX('%[0-9]%',aa)-1)+reverse(substring(reverse(aa),1,PATINDEX('%[0-9]%', reverse(aa))-1))
where PATINDEX('%[0-9]%', aa)>0
declare @a varchar(8000)
declare @b int
declare @c int
declare @d int
set @a='faeiureianen1ilasudfiu845isaeji74ir0'
set @b=0
while (@b<=9)
begin
set @a=replace(@a,cast(@b as varchar(1)),'*')
set @b=@b+1
endselect @a
returns varchar(8000)
as
begin
declare @b int
declare @c int
declare @d int
set @b=0
while (@b<=9)
begin
set @a=replace(@a,cast(@b as varchar(1)),'*')
set @b=@b+1
end
return @a
endselect abc(字段) from t
CREATE function dbo.regexReplace
(@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
endreturn @result
endselect aa='***123456**65658**77' into #a
select * from #a
select dbo.regexReplace(aa,'[*]','',1,1) from #a
drop table #a