高手们这样的相对定位字符串查找替换能实现吗

Question

本帖最后由 wl_xc 于 2010-11-21 00:37:27 编辑

Answer 1

if object_id('f_findstr') is not null drop function f_findstr
go
create function f_findstr(@s varchar(8000),@find varchar(10),@index int)
returns int
as
begin
declare @startindex int
set @startindex=0
while @index>0
begin
if charindex(@find,@s,@startindex)>0
set @startindex=charindex(@find,@s,@startindex+1)
set @index=@index-1
end
return @startindex
end
go
if not object_id('tb') is null
drop table tb
Go
Create table tb([a] nvarchar(20),[b] nvarchar(20))
Insert tb
select N',1,2,3,3,4,',N',59,67,72,83,9,'union all
select N',1,22,3,3,4,',N',59,72,83,9,'  union all
select N',1,2333,3,3,4,',N',59,55,83,72,'
Go
select 
       stuff([a],dbo.f_findstr([a],',',len(left([b],charindex(',72',[b])))-len(replace(left([b],charindex(',72',[b])),',',''))),
       dbo.f_findstr([a],',',len(left([b],charindex(',72',[b])))-len(replace(left([b],charindex(',72',[b])),',',''))+1)-
       dbo.f_findstr([a],',',len(left([b],charindex(',72',[b])))-len(replace(left([b],charindex(',72',[b])),',',''))),',')  
from tb
/*
,1,2,,3,4,
,1,,3,3,4,
,1,2333,3,,4,(3 行受影响)
*/

Answer 2

select aa=substring(a,number,charindex(',',a+',',number+1)-number),bb=substring(b,number,charindex(',',b+',',number+1)-number)  from tb,master..spt_values where type='p' and  substrin(a,number,1)=0