刚才给的替换的不对。。
DECLARE @str VarChar(1000)
Set @str = '213,12311213,2131'
Select isnull(stuff(Replace(','+@str,',213',''),1,1,''),'')
如果是这样。得到的结果是:
123112131
我要是结果:
12311213,2131
DECLARE @str VarChar(1000)
Set @str = '213,12311213,2131'
Select isnull(stuff(Replace(','+@str,',213',''),1,1,''),'')
如果是这样。得到的结果是:
123112131
我要是结果:
12311213,2131
Set @str = '213,12311213,2131' select substring(Replace(','+@str+',',',213,',''),2,len(Replace(','+@str+',',',213,',''))-2)
代码:
DECLARE @str VarChar(1000)
Set @str = '1,2,3,4,11,123,121' select substring(Replace(','+@str+',',',1,',''),2,len(Replace(','+@str+',',',1,',''))-2)得到的结果:
,3,4,11,123,121
实际要得到的结果:
2,3,4,11,123,121大哥。。我的字符串
1、不是固定的。
2、长度也不是固定的。
select @str = '1,2,3,4,11,123,121,12' ,@str2='1'
set @str=Replace(','+@str+',',','+@str2+',','')
set @str=case when charindex(',',@str)=1 then stuff(@str,1,1,'') else @str end
set @str=reverse(@str)
set @str=case when charindex(',',@str)=1 then stuff(@str,1,1,'') else @str end
set @str=reverse(@str)
select @str
select @str = '1,11,222,213,211,2341,342,34531' ,@str2='213'
set @str=Replace(','+@str+',',','+@str2+',','')
set @str=case when charindex(',',@str)=1 then stuff(@str,1,1,'') else @str end
set @str=reverse(@str)
set @str=case when charindex(',',@str)=1 then stuff(@str,1,1,'') else @str end
set @str=reverse(@str)
select @str结果:1,11,222211,2341,342,34531
.....................................
returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str='',@str1=@str1+','
while charindex(',',@str1)>1
begin
if(left(@str1,charindex(',',@str1)-1)=@str2)
set @str1=right(@str1,len(@str1)-charindex(',',@str1))
else
begin
select @str=@str+','+left(@str1,charindex(',',@str1)-1),
@str1=right(@str1,len(@str1)-charindex(',',@str1))
end
end
return stuff(@str,1,1,'')
end
DECLARE @str VarChar(1000) ,@str2 varchar(20)
select @str = '1,11,222,213,211,2341,342,34531' ,@str2='213'
select dbo.f_name(@str,@str2)