字符串处理

最新推荐文章于 2024-09-23 15:55:53 发布

wlzd3636

最新推荐文章于 2024-09-23 15:55:53 发布

阅读量329

点赞数 1

分类专栏： t_sql _查询文章标签： join xml c

本文链接：https://blog.csdn.net/wlzd3636/article/details/3984280

版权

t_sql _查询专栏收录该内容

4 篇文章 0 订阅

订阅专栏



dad(0d)da(da)da()ss
xxxxx(2弄)(3路)xxx()xxx

去掉()内的字符

--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)

--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)

---200请参考第一种 用不等于连接 +临时表

ps:
小技巧 如果想根据物理顺序来 排序 2005 可以 用 
row_number()over(partition by  column_name order by (select 1))
(select 1)
避免用 identity 带来的麻烦