TSQL Removing HTML tags from a field

Posted on 2006-04-11
Last Modified: 2008-03-17
Hello Experts,
I'm not sure if this is possible but i'm trying to read a text field into a temporary table and get rid of all html tags in it.

select substring(descriprion,1,200) into #temp  from myTable

I want to get rid of html tags that might exist in that column. the problem is I can not simply use Replace() and have it hardcoded since it may contain different HTML (e.g. <font color=red size=20> my description </font>)

I'm pretty much lookin for suggestions to achieve this

Thanks in advance
Question by:roddios
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >I'm not sure if this is possible but i'm trying to read a text field into a temporary table and get rid of all html tags in it.
    TSQL is not really the best idea to do this job, I would rather use some other programming/scripting language, using the MSXML documents objects to get rid of the html tags...
    LVL 4

    Accepted Solution

    you can use CHARINDEX to find the position of "<", the CHARINDEX to find the position of ">" then remove everything in between.  this assumes that neither "<" nor ">" is a valid character other than as tags


    Author Comment

    i would rather do it myself in but that's what i was asked to do.

    Thanks g_johnson, didn't know those functions exist! (CHARINDEX and PATINDEX)

    Here's the code if somebody's interested. it's a dumb code but enough for my purposes

    @myStr VARCHAR(2000) =''
    RETURNS varchar(2000)
    DECLARE @result VARCHAR(2000)
    DECLARE @index INT
    DECLARE @leftPos INT
    DECLARE @rightPos INT
    DECLARE @toBeRemovedStr VARCHAR(2000)
    SET @index = patindex('%<[A-Z,/]%>%', @myStr)
    WHILE (@index <> 0)
    set @leftPos = @index
    set @rightPos = charindex('>',subString(@myStr,@index, len(@myStr)))
    set @toBeRemovedStr = substring(@myStr,@leftPos,@rightPos)
    set @myStr = replace(@myStr,@toBeRemovedStr,'')
    SET @index = patindex('%<[A-Z,/]%>%', @myStr)
    select @result = @myStr
    return ltrim(rtrim(@result))
    LVL 4

    Expert Comment

    glad I could help

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how the fundamental information of how to create a table.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now