Remove html tags from a column

Question

I have the a column in my table which stores a paragraph like below :

<p>I like it.</p>this is my job.<main>current.</main>

I want to remove the tags <p>, </p>, and and all tags between < and >. So my expected output will be like below :

I like it. this is my job. current.

Answer 1

please try this

DECLARE @txt NVARCHAR(MAX) = '<p>I like it.</p>this is my job.<main>current.</main>'

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

this will help to remove all tags