How to strip HTML tags from a string in SQL Server

Question

I ve got data in SQL Server 2005 that contains HTML tags and I d like to strip all that out  leaving just the text between the tags  Ideally also replacing things like  amp lt  with  lt   etc  Is there an easy way to do this or has someone already got some sample T-SQL code  I don t have the ability to add extended stored procs and the like  so would prefer a pure T-SQL approach  preferably one backwards compatible with SQL 2000   I just want to retrieve the data with stripped out HTML  not update it  so ideally it would be written as a user-defined function  to make for easy reuse  So for example converting this   lt B gt Some useful text lt  B gt  amp nbsp   lt A onclick  quot return openInfo this  quot     href  quot http   there com 3ce984e88d0531bac5349 quot     target globalhelp gt      lt IMG title  quot Source Description quot  height 15 alt  quot Source Description quot           src  quot  ri new info gif quot  width 15 align top border 0 gt   lt  A gt  amp gt  amp nbsp  lt b gt more text lt  b gt  lt  TD gt  lt  TR gt   to this  Some useful text  gt  more text

User · Answer

Try this if you don t want to use the UDF function  SELECT COLUMN1  TRY CONVERT xml  COLUMN2  value       nvarchar max    as COL2  COLUMN3 FROM   DBO TABLENAME

User · Answer

Derived from  Goner Doug answer  with a few things updated   - using REPLACE where possible  - conversion of predefined entities like  amp eacute   I chose the ones I needed  -   - some conversion of list tags  lt ul gt  and  lt li gt     ALTER FUNCTION  dbo   udf StripHTML  --by Patrick Honorez --- www idevlop com --inspired by http   stackoverflow com questions 457701 best-way-to-strip-html-tags-from-a-string-in-sql-server 39253602 39253602    HTMLText varchar MAX    RETURNS varchar MAX  AS BEGIN DECLARE  Start  int DECLARE  End    int DECLARE  Length int  set  HTMLText   replace  htmlText    lt br gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt br  gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt br   gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt li gt    -    set  HTMLText   replace  htmlText    lt  li gt   CHAR 13    CHAR 10    set  HTMLText   replace  htmlText    amp rsquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp quot   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp amp   collate Latin1 General CS AS    amp    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp euro   collate Latin1 General CS AS         collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp lt   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp gt   collate Latin1 General CS AS    gt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp oelig   collate Latin1 General CS AS   oe   collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp nbsp   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp copy   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp laquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp reg   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp plusmn   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp sup2   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp sup3   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp micro   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp middot   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ordm   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp raquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac14   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac12   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac34   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Aelig  collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ccedil   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Egrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Eacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ecirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ouml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp agrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp acirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp auml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp aelig   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ccedil   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp egrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp eacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ecirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp euml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp icirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ocirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ouml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp divide   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp oslash   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ugrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp uacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ucirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp uuml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp quot   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp amp   collate Latin1 General CS AS    amp    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp lsaquo   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp rsaquo   collate Latin1 General CS AS    gt    collate Latin1 General CS AS    -- Remove anything between  lt STYLE gt  tags SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt     HTMLText     7 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt  STYLE gt     HTMLText     7 SET  Length     End -  Start    1 END  -- Remove anything between  lt whatever gt  tags SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1 END  RETURN LTRIM RTRIM  HTMLText    END

User · Answer

Here s an updated version of this function that incorporates the RedFilter answer  Pinal s original  with the LazyCoders additions and the goodeye typo corrections AND my own addition to handle in-line  lt STYLE gt  tags inside the HTML  ALTER FUNCTION  dbo   udf StripHTML     HTMLText varchar MAX    RETURNS varchar MAX  AS BEGIN DECLARE  Start  int DECLARE  End    int DECLARE  Length int  -- Replace the HTML entity  amp amp  with the   amp   character  this needs to be done first  as --   amp   might be double encoded as   amp amp amp    SET  Start   CHARINDEX   amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    amp    SET  Start   CHARINDEX   amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp lt  with the   lt   character SET  Start   CHARINDEX   amp lt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    lt    SET  Start   CHARINDEX   amp lt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp gt  with the   gt   character SET  Start   CHARINDEX   amp gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    gt    SET  Start   CHARINDEX   amp gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp amp  with the   amp   character SET  Start   CHARINDEX   amp amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    amp    SET  Start   CHARINDEX   amp amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp nbsp  with the     character SET  Start   CHARINDEX   amp nbsp     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length       SET  Start   CHARINDEX   amp nbsp     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1 END  -- Replace any  lt br gt  tags with a newline SET  Start   CHARINDEX   lt br gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace any  lt br  gt  tags with a newline SET  Start   CHARINDEX   lt br  gt     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br  gt     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace any  lt br   gt  tags with a newline SET  Start   CHARINDEX   lt br   gt     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br   gt     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1 END  -- Remove anything between  lt STYLE gt  tags SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt     HTMLText     7 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt  STYLE gt     HTMLText     7 SET  Length     End -  Start    1 END  -- Remove anything between  lt whatever gt  tags SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1 END  RETURN LTRIM RTRIM  HTMLText    END

User · Answer

Patrick Honorez code needs a slight change   It returns incomplete results for html that contains  amp lt  or  amp gt   This is because the code below the section      -- Remove anything between  tags   will in fact replace the  lt    to nothing  The fix is to the apply the below two lines at the end   set  HTMLText   replace  htmlText    amp lt   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp gt   collate Latin1 General CS AS    gt    collate Latin1 General CS AS

User · Answer

There is a UDF that will do that described here    User Defined Function to Strip HTML  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   lt    HTMLText      SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText       SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0     BEGIN         SET  HTMLText   STUFF  HTMLText  Start  Length             SET  Start   CHARINDEX   lt    HTMLText          SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText           SET  Length     End -  Start    1     END     RETURN LTRIM RTRIM  HTMLText   END GO   Edit  note this is for SQL Server 2005  but if you change the keyword MAX to something like 4000  it will work in SQL Server 2000 as well

User · Answer

How about using XQuery with a one liner   DECLARE  MalformedXML xml   StrippedText varchar max  SET  MalformedXML    xml query  for  x in     return   x   text     SET  StrippedText   CAST  MalformedXML as varchar max     This loops through all elements and returns the text   only   To avoid text between  elements concatenating without spaces  use   DECLARE  MalformedXML xml   StrippedText varchar max  SET  MalformedXML    xml query  for  x in     return concat    x   text    1         SET  StrippedText   CAST  MalformedXML as varchar max     And to respond to  How do you use this for a column     SELECT CAST html column query  for  x in     return concat    x   text    as varchar max     FROM table   For the above code  ensure your html column is of data type xml  if not  you need to save a casted version of the html as xml   I would do this as a separate exercise when you are loading HTML data  as SQL will throw an error if it finds malformed xml  e g  mismatched start end tags  invalid characters     These are excellent for when you want to build seachh phrases  strip HTML  etc   Just note that this returns type xml  so CAST or COVERT to text where appropriate   The xml version of this data type is useless  as it is not a well formed XML

User · Answer

There is a UDF that will do that described here    User Defined Function to Strip HTML  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   lt    HTMLText      SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText       SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0     BEGIN         SET  HTMLText   STUFF  HTMLText  Start  Length             SET  Start   CHARINDEX   lt    HTMLText          SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText           SET  Length     End -  Start    1     END     RETURN LTRIM RTRIM  HTMLText   END GO   Edit  note this is for SQL Server 2005  but if you change the keyword MAX to something like 4000  it will work in SQL Server 2000 as well

User · Answer

Here s an updated version of this function that incorporates the RedFilter answer  Pinal s original  with the LazyCoders additions and the goodeye typo corrections AND my own addition to handle in-line  lt STYLE gt  tags inside the HTML  ALTER FUNCTION  dbo   udf StripHTML     HTMLText varchar MAX    RETURNS varchar MAX  AS BEGIN DECLARE  Start  int DECLARE  End    int DECLARE  Length int  -- Replace the HTML entity  amp amp  with the   amp   character  this needs to be done first  as --   amp   might be double encoded as   amp amp amp    SET  Start   CHARINDEX   amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    amp    SET  Start   CHARINDEX   amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp lt  with the   lt   character SET  Start   CHARINDEX   amp lt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    lt    SET  Start   CHARINDEX   amp lt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp gt  with the   gt   character SET  Start   CHARINDEX   amp gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    gt    SET  Start   CHARINDEX   amp gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp amp  with the   amp   character SET  Start   CHARINDEX   amp amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length    amp    SET  Start   CHARINDEX   amp amp amp     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace the HTML entity  amp nbsp  with the     character SET  Start   CHARINDEX   amp nbsp     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length       SET  Start   CHARINDEX   amp nbsp     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1 END  -- Replace any  lt br gt  tags with a newline SET  Start   CHARINDEX   lt br gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br gt     HTMLText  SET  End    Start   3 SET  Length     End -  Start    1 END  -- Replace any  lt br  gt  tags with a newline SET  Start   CHARINDEX   lt br  gt     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br  gt     HTMLText  SET  End    Start   4 SET  Length     End -  Start    1 END  -- Replace any  lt br   gt  tags with a newline SET  Start   CHARINDEX   lt br   gt     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length  CHAR 13    CHAR 10   SET  Start   CHARINDEX   lt br   gt     HTMLText  SET  End    Start   5 SET  Length     End -  Start    1 END  -- Remove anything between  lt STYLE gt  tags SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt     HTMLText     7 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt  STYLE gt     HTMLText     7 SET  Length     End -  Start    1 END  -- Remove anything between  lt whatever gt  tags SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1 END  RETURN LTRIM RTRIM  HTMLText    END

User · Answer

Try this   It s a modified version of the one posted by RedFilter     this SQL removes all tags except BR  B  and P with any accompanying attributes  CREATE FUNCTION  dbo   StripHtml    HTMLText VARCHAR MAX   RETURNS VARCHAR MAX  AS BEGIN  DECLARE  Start  INT  DECLARE  End    INT  DECLARE  Length INT  DECLARE  TempStr varchar 255    SET  Start   CHARINDEX   lt    HTMLText   SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText    SET  Length     End -  Start    1   WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN    IF  UPPER SUBSTRING  HTMLText   Start  3    lt  gt    lt BR   AND  UPPER SUBSTRING  HTMLText   Start  2    lt  gt    lt P   AND  UPPER SUBSTRING  HTMLText   Start  2    lt  gt    lt B   AND  UPPER SUBSTRING  HTMLText   Start  3    lt  gt    lt  B      BEGIN       SET  HTMLText   STUFF  HTMLText  Start  Length        END         SET  Start   CHARINDEX   lt    HTMLText   End     SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText   Start      SET  Length     End -  Start  - 1  END   RETURN RTRIM LTRIM  HTMLText   END

User · Answer

How about using XQuery with a one liner   DECLARE  MalformedXML xml   StrippedText varchar max  SET  MalformedXML    xml query  for  x in     return   x   text     SET  StrippedText   CAST  MalformedXML as varchar max     This loops through all elements and returns the text   only   To avoid text between  elements concatenating without spaces  use   DECLARE  MalformedXML xml   StrippedText varchar max  SET  MalformedXML    xml query  for  x in     return concat    x   text    1         SET  StrippedText   CAST  MalformedXML as varchar max     And to respond to  How do you use this for a column     SELECT CAST html column query  for  x in     return concat    x   text    as varchar max     FROM table   For the above code  ensure your html column is of data type xml  if not  you need to save a casted version of the html as xml   I would do this as a separate exercise when you are loading HTML data  as SQL will throw an error if it finds malformed xml  e g  mismatched start end tags  invalid characters     These are excellent for when you want to build seachh phrases  strip HTML  etc   Just note that this returns type xml  so CAST or COVERT to text where appropriate   The xml version of this data type is useless  as it is not a well formed XML

User · Answer

While Arvin Amir s answer comes close to a full one-line solution you can drop in anywhere  he s got a slight bug in his select statement  missing the end of the line   and I wanted to handle the most common character references   What I ended up doing was this   SELECT replace replace replace CAST CAST replace  columnNameHere     amp      amp amp    as xml  query  for  x in     return concat    x   text    1         as varchar max      amp amp      amp       amp nbsp            amp  x20         FROM  tableName    Without the character reference code it can be simplified to this   SELECT CAST CAST  columnNameHere  as xml  query  for  x in     return concat    x   text    1         as varchar max   FROM  tableName

User · Answer

While Arvin Amir s answer comes close to a full one-line solution you can drop in anywhere  he s got a slight bug in his select statement  missing the end of the line   and I wanted to handle the most common character references   What I ended up doing was this   SELECT replace replace replace CAST CAST replace  columnNameHere     amp      amp amp    as xml  query  for  x in     return concat    x   text    1         as varchar max      amp amp      amp       amp nbsp            amp  x20         FROM  tableName    Without the character reference code it can be simplified to this   SELECT CAST CAST  columnNameHere  as xml  query  for  x in     return concat    x   text    1         as varchar max   FROM  tableName

User · Answer

Here is a version that doesn t require an UDF and works even if the HTML contains tags without matching closing tags   TRY CAST REPLACE REPLACE REPLACE  HtmlCol     gt       gt        lt       lt      --  gt     -- gt    AS XML  value       NVARCHAR MAX

User · Answer

Derived from  Goner Doug answer  with a few things updated   - using REPLACE where possible  - conversion of predefined entities like  amp eacute   I chose the ones I needed  -   - some conversion of list tags  lt ul gt  and  lt li gt     ALTER FUNCTION  dbo   udf StripHTML  --by Patrick Honorez --- www idevlop com --inspired by http   stackoverflow com questions 457701 best-way-to-strip-html-tags-from-a-string-in-sql-server 39253602 39253602    HTMLText varchar MAX    RETURNS varchar MAX  AS BEGIN DECLARE  Start  int DECLARE  End    int DECLARE  Length int  set  HTMLText   replace  htmlText    lt br gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt br  gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt br   gt   CHAR 13    CHAR 10   set  HTMLText   replace  htmlText    lt li gt    -    set  HTMLText   replace  htmlText    lt  li gt   CHAR 13    CHAR 10    set  HTMLText   replace  htmlText    amp rsquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp quot   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp amp   collate Latin1 General CS AS    amp    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp euro   collate Latin1 General CS AS         collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp lt   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp gt   collate Latin1 General CS AS    gt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp oelig   collate Latin1 General CS AS   oe   collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp nbsp   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp copy   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp laquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp reg   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp plusmn   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp sup2   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp sup3   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp micro   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp middot   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ordm   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp raquo   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac14   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac12   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp frac34   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Aelig  collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ccedil   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Egrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Eacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ecirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp Ouml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp agrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp acirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp auml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp aelig   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ccedil   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp egrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp eacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ecirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp euml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp icirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ocirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ouml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp divide   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp oslash   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ugrave   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp uacute   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp ucirc   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp uuml   collate Latin1 General CS AS        collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp quot   collate Latin1 General CS AS       collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp amp   collate Latin1 General CS AS    amp    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp lsaquo   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp rsaquo   collate Latin1 General CS AS    gt    collate Latin1 General CS AS    -- Remove anything between  lt STYLE gt  tags SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt     HTMLText     7 SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt STYLE    HTMLText  SET  End   CHARINDEX   lt  STYLE gt     HTMLText  CHARINDEX   lt  STYLE gt     HTMLText     7 SET  Length     End -  Start    1 END  -- Remove anything between  lt whatever gt  tags SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1  WHILE   Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN SET  HTMLText   STUFF  HTMLText   Start   Length      SET  Start   CHARINDEX   lt     HTMLText  SET  End   CHARINDEX   gt     HTMLText  CHARINDEX   lt     HTMLText   SET  Length     End -  Start    1 END  RETURN LTRIM RTRIM  HTMLText    END

User · Answer

There is a UDF that will do that described here    User Defined Function to Strip HTML  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   lt    HTMLText      SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText       SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0     BEGIN         SET  HTMLText   STUFF  HTMLText  Start  Length             SET  Start   CHARINDEX   lt    HTMLText          SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText           SET  Length     End -  Start    1     END     RETURN LTRIM RTRIM  HTMLText   END GO   Edit  note this is for SQL Server 2005  but if you change the keyword MAX to something like 4000  it will work in SQL Server 2000 as well

User · Answer

This is not a complete new solution but a correction for afwebservant s solution   --note comments to see the corrections  CREATE FUNCTION  dbo   StripHTML    HTMLText VARCHAR MAX     RETURNS VARCHAR MAX    AS   BEGIN    DECLARE  Start  INT    DECLARE  End    INT    DECLARE  Length INT    --DECLARE  TempStr varchar 255   this is not used      SET  Start   CHARINDEX   lt    HTMLText     SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText      SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0    BEGIN      IF  UPPER SUBSTRING  HTMLText   Start  4    lt  gt    lt BR gt    AND  UPPER SUBSTRING  HTMLText   Start  5    lt  gt    lt  BR gt          begin         SET  HTMLText   STUFF  HTMLText  Start  Length             end   -- this ELSE and SET is important    ELSE         SET  Length   0     -- minus  Length here below is important    SET  Start   CHARINDEX   lt    HTMLText   End- Length       SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText   Start     -- instead of -1 it should be  1    SET  Length     End -  Start    1    END     RETURN RTRIM LTRIM  HTMLText     END

User · Answer

Here is a version that doesn t require an UDF and works even if the HTML contains tags without matching closing tags   TRY CAST REPLACE REPLACE REPLACE  HtmlCol     gt       gt        lt       lt      --  gt     -- gt    AS XML  value       NVARCHAR MAX

User · Answer

This is not a complete new solution but a correction for afwebservant s solution   --note comments to see the corrections  CREATE FUNCTION  dbo   StripHTML    HTMLText VARCHAR MAX     RETURNS VARCHAR MAX    AS   BEGIN    DECLARE  Start  INT    DECLARE  End    INT    DECLARE  Length INT    --DECLARE  TempStr varchar 255   this is not used      SET  Start   CHARINDEX   lt    HTMLText     SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText      SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0    BEGIN      IF  UPPER SUBSTRING  HTMLText   Start  4    lt  gt    lt BR gt    AND  UPPER SUBSTRING  HTMLText   Start  5    lt  gt    lt  BR gt          begin         SET  HTMLText   STUFF  HTMLText  Start  Length             end   -- this ELSE and SET is important    ELSE         SET  Length   0     -- minus  Length here below is important    SET  Start   CHARINDEX   lt    HTMLText   End- Length       SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText   Start     -- instead of -1 it should be  1    SET  Length     End -  Start    1    END     RETURN RTRIM LTRIM  HTMLText     END

User · Answer

Patrick Honorez code needs a slight change   It returns incomplete results for html that contains  amp lt  or  amp gt   This is because the code below the section      -- Remove anything between  tags   will in fact replace the  lt    to nothing  The fix is to the apply the below two lines at the end   set  HTMLText   replace  htmlText    amp lt   collate Latin1 General CS AS    lt    collate Latin1 General CS AS  set  HTMLText   replace  htmlText    amp gt   collate Latin1 General CS AS    gt    collate Latin1 General CS AS

User · Answer

There is a UDF that will do that described here    User Defined Function to Strip HTML  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   lt    HTMLText      SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText       SET  Length     End -  Start    1     WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0     BEGIN         SET  HTMLText   STUFF  HTMLText  Start  Length             SET  Start   CHARINDEX   lt    HTMLText          SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText           SET  Length     End -  Start    1     END     RETURN LTRIM RTRIM  HTMLText   END GO   Edit  note this is for SQL Server 2005  but if you change the keyword MAX to something like 4000  it will work in SQL Server 2000 as well

User · Answer

If your HTML is well formed  I think this is a better solution   create function dbo StripHTML   text varchar max    returns varchar max  as begin     declare  textXML xml     declare  result varchar max      set  textXML   REPLACE   text    amp              with doc contents  as               select chunks chunk query      from  textXML nodes      as chunks chunk            select  result   contents value       varchar max    from doc     return  result end go  select dbo StripHTML  This  lt i gt is lt  i gt  an  lt b gt html lt  b gt  test

User · Answer

Try this   It s a modified version of the one posted by RedFilter     this SQL removes all tags except BR  B  and P with any accompanying attributes  CREATE FUNCTION  dbo   StripHtml    HTMLText VARCHAR MAX   RETURNS VARCHAR MAX  AS BEGIN  DECLARE  Start  INT  DECLARE  End    INT  DECLARE  Length INT  DECLARE  TempStr varchar 255    SET  Start   CHARINDEX   lt    HTMLText   SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText    SET  Length     End -  Start    1   WHILE  Start  gt  0 AND  End  gt  0 AND  Length  gt  0  BEGIN    IF  UPPER SUBSTRING  HTMLText   Start  3    lt  gt    lt BR   AND  UPPER SUBSTRING  HTMLText   Start  2    lt  gt    lt P   AND  UPPER SUBSTRING  HTMLText   Start  2    lt  gt    lt B   AND  UPPER SUBSTRING  HTMLText   Start  3    lt  gt    lt  B      BEGIN       SET  HTMLText   STUFF  HTMLText  Start  Length        END         SET  Start   CHARINDEX   lt    HTMLText   End     SET  End   CHARINDEX   gt    HTMLText CHARINDEX   lt    HTMLText   Start      SET  Length     End -  Start  - 1  END   RETURN RTRIM LTRIM  HTMLText   END

User · Answer

If your HTML is well formed  I think this is a better solution   create function dbo StripHTML   text varchar max    returns varchar max  as begin     declare  textXML xml     declare  result varchar max      set  textXML   REPLACE   text    amp              with doc contents  as               select chunks chunk query      from  textXML nodes      as chunks chunk            select  result   contents value       varchar max    from doc     return  result end go  select dbo StripHTML  This  lt i gt is lt  i gt  an  lt b gt html lt  b gt  test

User · Answer

Try this if you don t want to use the UDF function  SELECT COLUMN1  TRY CONVERT xml  COLUMN2  value       nvarchar max    as COL2  COLUMN3 FROM   DBO TABLENAME

[html] How to strip HTML tags from a string in SQL Server?

Examples related to html

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to sql-server-2005