我有以下一个字符串|0100|0200|0300|0400
现在需要在存储过程中替换|0100,但是|0100里面的后两位00是不确定的,可能为|0123,|0134,请教各位,怎样实现只要找到|01这个前缀,就可以把|0100这部分替换成我想要的内容例如|3333
现在需要在存储过程中替换|0100,但是|0100里面的后两位00是不确定的,可能为|0123,|0134,请教各位,怎样实现只要找到|01这个前缀,就可以把|0100这部分替换成我想要的内容例如|3333
set @s='|0100|0200|0300|0120'while patindex('%|01[0-9][0-9]%',@s)>0
begin
set @s=stuff(@s,patindex('%|01[0-9][0-9]%',@s),5,'|3333')
end
select @s不知道对不
stuff(col,1,3,'|3333')
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( col varchar(100))
go
insert tb SELECT '|0100|0200|0300|0400'
UNION ALL SELECT '|0120|0200|0300|0400'
UNION ALL SELECT '|0120|0200|0403|0400'
UNION ALL SELECT '|0220|0200|0403|0400'
go
update tb
set col=REPLACE(STUFF(col,patINDEX('%|01%',col),5,'k'),'k','|3333')
WHERE patINDEX('%|01%',col)>0
go
SELECT * FROM tb
/*
----------------------------------------------------------------------------------------------------
|3333|0200|0300|0400
|3333|0200|0300|0400
|3333|0200|0403|0400
|0220|0200|0403|0400
*/
@reStr varchar(100),
@findstr varchar(100)
set @str='asdf|0134|0200|0300|0400'
set @findstr='|01__|'
set @reStr='|3333|'
declare @pos int,@len int
set @len=len(@findStr)
set @pos=patindex('%'+@findstr+'%',@str)
set @str=stuff(@str,@pos,@len,@reStr)
select @str
set @s='|0100|0200|0300|0400'select replace(stuff(@s,charindex('|01',@s)+len('|01'),2,''),'|01','|3333')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|3333|0200|0300|0400(1 行受影响)*/
set @s='|0123|0200|0300|0400'
select stuff(@s,charindex('|01',@s),5,'|3333')
eclare @s varchar(200)
declare @i int
set @s='|0100|0200|0300|0400'
set @i=5
select replace(stuff(@s,charindex('|0'+@i,@s)+len('|0'+@i),2,''),'|0'+@i,'|3333')
declare @i int
set @s='|0100|0200|0300|0400'
set @i=1
select replace(stuff(@s,charindex('|0'+LTRIM(@i),@s)+len('|0'+LTRIM(@i)),2,''),'|0'+LTRIM(@i),'|3333')
WHERE charindex('|0'+LTRIM(@i),@s)>0
/*
|3333|0200|0300|0400(1 行受影响)
*/