SQL Serer - Strip out HTML code from varchar field...

I have an old database that I am migrating data from.
One of the fields is a varchar with text like this in it:-

<p><span style="font-size: 9.5pt">A String Quartet with a twist! Stringfever are a musical group who combine the traditional and the futuristic; the unquestionable style of the classically trained fused with the funky and modern. Their abilities range from classical string skills to beat-boxing and plenty in between.</span></p>  <div style="line-height: normal; margin: 0cm 0cm 10pt"><span style="font-size: 9.5pt">Since 2004, Stringfever have performed their after dinner show at over 700 major corporate events throughout Europe, including&nbsp;Gala dinners, Award Ceremonies, Christmas Parties and&nbsp;Conferences. Their show usually lasts around 30 minutes and is equally suited as a grand finale to an evening or for lifting the mood of an audience before an award ceremony or presentation.</span></div>  <div style="line-height: normal; margin: 0cm 0cm 10pt"><span style="font-size: 9.5pt">Stringfever is made up of:</span></div>  <div style="line-height: normal; margin: 0cm 0cm 10pt"><b><span style="font-size: 9.5pt">Giles Broadbent</span></b><span style="font-size: 9.5pt"><br />  Leader of the pack. Former West End konzertmeister. Royal Academy of Music, gypsy violin demon, you name it, he'll play it - the faster the better. Favourite sports: skiing, cordless bungee jumping. Plays a bright blue 5 string Violectra.</span></div>  <div style="line-height: normal; margin: 0cm 0cm 10pt"><b><span style="font-size: 9.5pt">Ralph Broadbent</span></b><span style="font-size: 9.5pt"><br />  MC and chief musical arranger. Royal Academy of Music. Less height than his band mates but more strings. Football mad. Plays a 6 string Violectra. </span></div>  <div style="line-height: normal; margin: 0cm 0cm 10pt"><b><span style="font-size: 9.5pt"><div>

Open in new window


As you can see it is a mess - years of rich text editors inserting tags here there and everywhere!

Is there a trick way to remove any text held between <...> tag delimiters?

Regards,
LVL 2
splantonAsked:
Who is Participating?
 
mikeyd234Connect With a Mentor Commented:
Here's a function to do just that:
CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

-- how to use
select dbo.udf_StripHTML(myTable.mycolumnThatContainsHTML) 

Open in new window

0
 
plusone3055Commented:
Splanton

the following link
http://sqlserverplanet.com/tsql/substring-between-two-characters 

will show an example as how to elimiante characters before, after, and inbetween chracters
0
 
mikeyd234Commented:
Tut tut +1 beating me again, though I have provided a nice function to strip html :D
0
 
splantonAuthor Commented:
Mikey,
I knew some clever soul would have got a function for this. Glad I didn't waste hours re-inventing the wheel.
Thank you for such a prompt, concise and clear answer.

Regards,
0
 
splantonAuthor Commented:
Oooops! Sorry guys, looks like I was allocating point whilst further answers came in.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.