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

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('<',@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

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.

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], '>', '/> '), '</', '<'), '--/>', '-->') AS XML).value('.', 'NVARCHAR(MAX)')

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.

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        

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], '&', '&') as xml).query('for $x in //. return concat((($x)//text())[1]," ")') as varchar(max)), '&', '&'), ' ', ' '), ' ', ' ')
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]

Patrick Honorez code needs a slight change.

It returns incomplete results for html that contains < or >

This is because the code below the section

-- Remove anything between tags

will in fact replace the < > to nothing. The fix is to the apply the below two lines at the end:

set @HTMLText = replace(@htmlText, '&lt;' collate Latin1_General_CS_AS, '<'  collate Latin1_General_CS_AS)
set @HTMLText = replace(@htmlText, '&gt;' collate Latin1_General_CS_AS, '>'  collate Latin1_General_CS_AS)