--2005
select * into ## from(select sring='xxxxx(2弄)(3路)xxx()xxx' union all select 'dad(0d)da(da)da()ss')x
--cte+nums 解决
with xwj
as
(select id=1
union all
select id+1 from xwj where id<=80
) ,
xwj2
as
(
select sring,id ,substring(sring,id,charindex(')',sring+')',id)-id+1)as sonsring
from ## as a inner join xwj as b on len(a.sring)>=id and substring(')'+sring,id,1)=')'
)
,
xwj3
as
(
select sring,id,px=row_number()over(partition by sring order by id),
substring(sonsring,1,charindex('(',sonsring)) +substring(sonsring,case when charindex(')',sonsring)=0 then 1 else charindex(')',sonsring)end ,len(sonsring)) as sonsring
from xwj2
)
,
xwj4
as
(select sring,px,cast(sonsring as varchar(500)) as sonsring from xwj3 where px=1
union all
select b.sring,b.px,cast(a.sonsring+b.sonsring as varchar(500)) from xwj4 as a inner join xwj3 as b on a.sring=b.sring and b.px=a.px+1
)
select sonsring as sring from xwj4 as a where px=(select max(px) from xwj3 as b where a.sring=b.sring)
结果
sring
----------
xxxxx()()xxx()xxx
dad()da()da()ss
--xml+cte
select * from ##
with
xwj
as
(
select x.sring,px=row_number()over(partition by sring order by (select 1)),
substring(v,1,charindex('(',v)) +substring(v,case when charindex(')',v)=0 then 1 else charindex(')',v)end ,len(v)) as sonsring
from
(select sring,cast('<row>'+replace(sring,')',')</row><row>')+'</row>' as xml) as sonsring
from ## )x
cross apply
(select v=t.c.value('.','sysname')
from x.sonsring.nodes('row') as t(c)
) as b
)
,
xwj2
as
(select sring,px,cast(sonsring as varchar(500)) as sonsring from xwj where px=1
union all
select b.sring,b.px,cast(a.sonsring+b.sonsring as varchar(500)) from xwj2 as a inner join xwj as b on a.sring=b.sring and a.px=b.px-1
)
select sonsring as sring from xwj2 as b where px=(select max(px) from xwj as a where a.sring=b.sring)
结果:
sring
----------
xxxxx()()xxx()xxx
dad()da()da()ss
第二个比第一个要好 速度快 io少