Dan Mann Dan Mann - 2 months ago 4x
HTML Question

SQL - Remove all HTML tags in a string

In my dataset, I have a field which stores text marked up with HTML. The general format is as follows:

<html><head></head><body><p>My text.</p></body></html>

I could attempt to solve the problem by doing the following:

REPLACE(REPLACE(Table.HtmlData, '<html><head></head><body><p>', ''), '</p></body></html>')

However, this is not a strict rule as some of entries break W3C Standards and do not include
tags for example. Even worse, there could be missing closing tags. So I would need to include the
function for each opening and closing tag that could exist.

'<html>', ''),
'</html>', ''),
'<head>', ''),
'</head>', ''),
'<body>', ''),
'</body>', ''),
'<p>', ''),
'</p>', '')

I was wondering if there was a better way to accomplish this than using multiple nested
functions. Unfortunately, the only languages I have available in this environment are SQL and Visual Basic (not .NET).

DECLARE @x XML = '<html><head></head><body><p>My text.</p></body></html>'

SELECT t.c.value('.', 'NVARCHAR(MAX)')
FROM @x.nodes('*') t(c)

Update - For strings with unclosed tags:

DECLARE @x NVARCHAR(MAX) = '<html><head></head><body><p>My text.<br>More text.</p></body></html>'

SELECT x.value('.', 'NVARCHAR(MAX)')
    SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), '</', '<') AS XML)
) r