Pulling out tags - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pulling out tags

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-14 : 15:58:30

so I've got a series of strings, with html tags inside of them. I want to disappear those tags.

basically I want to take a value like

'<stuff blah> more stuff and blah blah also some things </stuff blah> and then some words and stuff <br> and more stuff'

and filter it to become 'more stuff and blah blah also some things and then some words and stuff and more stuff'

so I'm taking out all '<'s and '>'s and everything between them.

I'm sure I'm going to need to use the substring and charindex function, but am unfamiliar with those functions and am without a firm idea of what to do here.

Thanks!!!

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-14 : 16:22:53

Why doesn't this work:
declare @myval varchar(1000)
set @myval='<1>2<3>4<5>6<7>8'
select REPLACE(@myval, '<%>', '')

????/

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-14 : 16:41:09

This works!

declare @myval varchar(1000)
set @myval='0<1>2<3>4<5>6<7>8'
select
@myval
,LEFT(@myval,CHARINDEX('<', @myval)-1) + right(@myval, len(@myval)-CHARINDEX('>',@myval))

now I just need it to do it over and over again untill all tags are removed!

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-14 : 17:31:09

I FIGURED IT OUT~!!~!~!~~~!!!! ALL ON MY OWN!!!!

declare @myval varchar(1000)
set @myval='Ian is silly <also there are hidden secretes> and ian loves fruit pie <because everyone knows he did it> but he
likes moonpie more and cake the most <super secrete fact, ian HATES cake>';

while LEN(@myval) > LEN(replace(@myval,'>',''))

begin
set @myval = (select LEFT(@myval,CHARINDEX('<', @myval)-1) + right(@myval, len(@myval)-CHARINDEX('>',@myval)))

continue

end;

select @myval

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 02:51:32

Another method

Declare @Text Varchar(1000),@delimiter NVARCHAR(5)
Set @Text = 'Ian is silly <also there are hidden secretes> and ian loves fruit pie <because everyone knows he did it> but he
likes moonpie more and cake the most <super secrete fact, ian HATES cake>'
set @text=replace(replace(@text,'<','~'),'>','~')
set @delimiter = '~'
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
select data from
(
SELECT row_number() over (order by (select 0)) as sno,T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
) as t
where sno%2=1

Madhivanan

Failing to plan is Planning to fail

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-15 : 12:07:29

Well that is certainly awesome, I do NOT understand it! :-P

Also, I need a one string result set for this project, yours breaks it down into a whole new column per string.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources