replace 急啊
Replace(str1, '%[0-9]% ', '* ')
怎么可以把字符串str1中的所用的数字全部代替成*号
上面写法怎么不行啊
[解决办法]
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)
[解决办法]
这样吧
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
end
select @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
end
select 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
end
return @result
end
select aa= '***123456**65658**77 ' into #a
select * from #a
select dbo.regexReplace(aa, '[*] ', ' ',1,1) from #a
drop table #a
------解决方案--------------------
replace(replace(replace(replace(@str1, '0 ', '* '), '1 ', '* '), '2 ', '* ')............
