Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Strip HTML Tags Expand / Collapse
Author
Message
Posted Friday, October 28, 2011 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
Guys,

Guys I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..

This is the one I used:
http://cosier.wordpress.com/2008/10/22/tsql-strip-html-function/

Any suggestion is helpful.

Thanks,
Laura


Strip Out :)
Post #1197668
Posted Sunday, October 30, 2011 12:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
Laura_SqlNovice (10/28/2011)
Guys,

Guys I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..

This is the one I used:
http://cosier.wordpress.com/2008/10/22/tsql-strip-html-function/

Any suggestion is helpful.

Thanks,
Laura


Strip Out :)


What do you want to do with the characters that remain? Just leave them in the string?

Also, what about individual lines? If there's a new line in the HTML, do you want a new row in the result set?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1197818
Posted Sunday, October 30, 2011 6:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 1,793, Visits: 5,793
is it HTML or XHTML?

also, +1 on Jeff's questions...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1197855
    Posted Sunday, October 30, 2011 7:18 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, July 31, 2014 7:40 PM
    Points: 103, Visits: 500
    Thanks jeff. Yes I just need the string with no HTML tags. IF there is new line I do not need another line, but need one string for each html string. Hope I am making sense. Thanks again.
    Post #1197864
    Posted Sunday, October 30, 2011 8:16 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 10:47 AM
    Points: 35,347, Visits: 31,882
    Understood. Let's nail the requirements right to the floor now. If you have the following string, what precisely do you want to get back?

    <data1>somedata1a</data1><data2>somedata2a</data2>
    <data1>somedata1b</data1><data2>somedata2b</data2>
    <data1>somedata1c</data1><data2>somedata2c</data2>



    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1197872
    Posted Sunday, October 30, 2011 8:49 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, July 31, 2014 7:40 PM
    Points: 103, Visits: 500
    Jeff Moden (10/30/2011)
    Understood. Let's nail the requirements right to the floor now. If you have the following string, what precisely do you want to get back?

    <data1>somedata1a</data1><data2>somedata2a</data2>
    <data1>somedata1b</data1><data2>somedata2b</data2>
    <data1>somedata1c</data1><data2>somedata2c</data2>



    If these strings are loaded in three rows then I need three rows with no html tags... I need to replace the html tags with '' on a column which has html text. The data type is ntext for that sql column.

    Thanks a lot.
    Post #1197881
    Posted Sunday, October 30, 2011 9:45 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 10:47 AM
    Points: 35,347, Visits: 31,882
    Got it. Thanks.

    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1197884
    Posted Sunday, October 30, 2011 11:34 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 10:47 AM
    Points: 35,347, Visits: 31,882
    I refactored the function that I found at the following URL... look for the answer posted by "dudeNumber4"...

     CREATE FUNCTION dbo.StripHtmlTags
    (@HtmlText XML )
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    RETURN (
    SELECT contents.value('.', 'nvarchar(max)')
    FROM (
    SELECT contents = chunks.chunk.query('.') FROM @HtmlText.nodes('/') AS chunks(chunk)
    ) doc
    )
    END
    ;

    I tried turning it into an iTVF instead of a scalar function and it took twice as long to run.


    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1197889
    Posted Monday, October 31, 2011 8:51 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, July 31, 2014 7:40 PM
    Points: 103, Visits: 500
    Thanks a lot Jeff. This is a good solution for well formatted html. Thanks a lot again Jeff.
    Post #1198105
    Posted Monday, October 31, 2011 9:34 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 3:45 PM
    Points: 1,793, Visits: 5,793
    Jeff Moden (10/30/2011)

    I tried turning it into an iTVF instead of a scalar function and it took twice as long to run.


    I also tried and it took half as long....


    CREATE FUNCTION dbo.StripHtmlTags2
    (@HtmlText XML )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT
    @HtmlText.value('(.)[1]', 'nvarchar(max)') AS result
    )

    Is this how yours ended up?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1198135
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse