create table tbTest(colTest nvarchar(max)) insert into tbTest select N'降龙十七掌@xxoo 我乐 个去 大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select * from tbTestselect distinct right(Col , case when charindex('@',Col)=0 then 0 else len(Col)- charindex('@',Col) end ) from ( SELECT SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) AS Col FROM tbTest as A JOIN master.dbo.spt_values AS B ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.colTest) AND SUBSTRING(' '+A.colTest,B.number,1)=' ' WHERE charindex('@', SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) )>0 ) as X /*aaa bbb ccc xxoo */
select right(Col , case when charindex('@',Col)=0 then 0 else len(Col)- charindex('@',Col) end ) from ( SELECT B.number, SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) AS Col FROM tbTest as A JOIN master.dbo.spt_values AS B ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.colTest) AND SUBSTRING(' '+A.colTest,B.number,1)=' ' WHERE charindex('@', SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) )>0 ) as X group by right(Col , case when charindex('@',Col)=0 then 0 else len(Col)- charindex('@',Col) end ) order by min(number)
declare @code nvarchar(1200) set @code=N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select distinct SUBSTRING(vx,charindex('@',vx),(len(vx)-charindex('@',vx)+1)) from ( select b.vx from (select cast('<root><v>'+REPLACE(@code,' ','</v><v>')+'</v></root>' as xml) as x ) a outer apply( select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v) ) b)c where
declare @code nvarchar(1200) set @code=N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select vx from ( select distinct SUBSTRING(b.vx,charindex('@',b.vx)+1,(len(b.vx)-charindex('@',b.vx)))vx from (select cast('<root><v>'+REPLACE(@code,' ','</v><v>')+'</v></root>' as xml) as x ) a outer apply( select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v) ) b)c where LEN (vx)>0 /* aaa bbb ccc */
感谢楼上两位回答,但是colTest是ntext类型的话,好像就有点问题了
create table tbTest(colTest ntext)insert into tbTest(colTest) select '大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa ' select distinct substring(a.colTest,b.number+1, charindex(' ',a.colTest,b.number)-b.number) 'colTest2' from tbTest a inner join master.dbo.spt_values b on b.[type]='P' and b.number<=datalength(a.colTest) where substring(a.colTest,b.number,1)='@'/* colTest2 ------------------- aaa bbb ccc (3 row(s) affected) */
给他转换一下 create table tb(code ntext) insert into tb select N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select vx from ( select distinct SUBSTRING(b.vx,charindex('@',b.vx)+1,(len(b.vx)-charindex('@',b.vx)))vx from (select cast('<root><v>'+REPLACE(CONVERT(NVARCHAR(1200),code),' ','</v><v>')+'</v></root>' as xml) as x from tb ) a outer apply( select vx=N.v.value('.','NVARCHAR(1200)') from a.x.nodes('/root/v') N(v) ) b)c where LEN (vx)>0 /* vx aaa bbb ccc */
create table tbTest(colTest nvarchar(max))
insert into tbTest select N'降龙十七掌@xxoo 我乐 个去 大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select * from tbTestselect distinct right(Col , case when charindex('@',Col)=0 then 0 else len(Col)- charindex('@',Col) end )
from
(
SELECT
SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) AS Col
FROM tbTest as A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.colTest)
AND SUBSTRING(' '+A.colTest,B.number,1)=' '
WHERE charindex('@', SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) )>0
) as X
/*aaa
bbb
ccc
xxoo
*/
from
(
SELECT B.number,
SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) AS Col
FROM tbTest as A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.colTest)
AND SUBSTRING(' '+A.colTest,B.number,1)=' '
WHERE charindex('@', SUBSTRING(A.colTest,B.number,CHARINDEX(' ',A.colTest+' ',B.number)-B.number) )>0
) as X
group by right(Col , case when charindex('@',Col)=0 then 0 else len(Col)- charindex('@',Col) end )
order by min(number)
set @code=N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select distinct SUBSTRING(vx,charindex('@',vx),(len(vx)-charindex('@',vx)+1)) from (
select b.vx
from
(select cast('<root><v>'+REPLACE(@code,' ','</v><v>')+'</v></root>' as xml) as x ) a
outer apply(
select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b)c where
set @code=N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select vx from (
select distinct SUBSTRING(b.vx,charindex('@',b.vx)+1,(len(b.vx)-charindex('@',b.vx)))vx
from
(select cast('<root><v>'+REPLACE(@code,' ','</v><v>')+'</v></root>' as xml) as x ) a
outer apply(
select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b)c where LEN (vx)>0
/*
aaa
bbb
ccc
*/
create table tbTest(colTest ntext)insert into tbTest(colTest)
select
'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa '
select distinct substring(a.colTest,b.number+1,
charindex(' ',a.colTest,b.number)-b.number) 'colTest2'
from tbTest a
inner join master.dbo.spt_values b
on b.[type]='P' and b.number<=datalength(a.colTest)
where substring(a.colTest,b.number,1)='@'/*
colTest2
-------------------
aaa
bbb
ccc (3 row(s) affected)
*/
给他转换一下
create table tb(code ntext)
insert into tb select N'大块html内容......@aaa 大块html内容......@bbb 大块html内容......@ccc 大块html内容......@aaa 'select vx from (
select distinct SUBSTRING(b.vx,charindex('@',b.vx)+1,(len(b.vx)-charindex('@',b.vx)))vx
from
(select cast('<root><v>'+REPLACE(CONVERT(NVARCHAR(1200),code),' ','</v><v>')+'</v></root>' as xml) as x from tb ) a
outer apply(
select vx=N.v.value('.','NVARCHAR(1200)') from a.x.nodes('/root/v') N(v)
) b)c where LEN (vx)>0
/*
vx
aaa
bbb
ccc
*/
如果是这样的内容就有点问题比如:@a<a><p>a</p>a我是初学,是不是转xml会有转义问题?谢谢了,有解决方法吗