Remove HTML tags from the field / variable

I have seen number of times people asking about the logic to remove HTML tags from the given text. I always ask rthem to change the logic to save that text at application so that tis situation of removing HTML tags never exist.

But, to fulfil the requirement I have written a script. Might help someone...

----------------------------------------------------------------------------------
-- To get the list of Tags removed....
Declare @vTagNameTable Table(Tag varchar(max))

-- My HTML
Declare @v varchar(max)

Set @v = 'Atif Sheikh <>Sheikh Atif <><><>>'

Declare @vCharI1 int -- Used to locate '<' Declare @vCharI2 int -- Used to locate '>' to get Tag Name
Declare @vCharICheck int -- Used to Check for Tag Name
Declare @vEndTagName varchar(1000)

Declare @vStartPos int
Set @vStartPos = 0

While 1=1
begin
Set @vCharI1 = CharIndex('<',@v,@vStartPos) if @vCharI1 > 0
begin
-- Get the Tag Name, if it is a HTML Tag
Set @vCharI2 = CharIndex('>',@v,@vCharI1 + 1)
if @vCharI2 > 0
begin
-- Check <>
if @vCharI2 = @vCharI1 + 1
begin
Set @vStartPos = @vCharI2 + 1
end
else
begin
Set @vCharICheck = CharIndex('<',@v,@vCharI1 + 1) if (@vCharICheck < @vCharI2) begin -- Last <> 0
Set @vStartPos = @vCharICheck
else
begin
-- @vCharICheck = 0
-- Its a Last TAG...
Set @vEndTagName = SubString(@v,@vCharI1,(@vCharI2-@vCharI1) + 1)
if Not Exists(Select 1 from @vTagNameTable where Tag = @vEndTagName)
begin
Insert into @vTagNameTable
Select @vEndTagName

Set @v = Replace(@v,@vEndTagName,'')
end
Set @vStartPos = @vCharI2
end

if @vStartPos = Len(@v)
begin
Select @vStartPos , Len(@v),@vCharICheck,@vCharI1
print 'Over Here....'
BREAK
end
end
else
begin
-- ITS A TAG....
Set @vEndTagName = SubString(@v,@vCharI1,(@vCharI2-@vCharI1) + 1)
if Not Exists(Select 1 from @vTagNameTable where Tag = @vEndTagName)
begin
Insert into @vTagNameTable
Select @vEndTagName

Set @v = Replace(@v,@vEndTagName,'')
end

--Set @vStartPos = @vCharI2 + 1
Set @vStartPos = @vCharI1
if @vStartPos > Len(@v)
begin
print 'Here....'
BREAK
end
end
end
end
else
BREAK -- No Tag
end
else
BREAK
end
Select * from @vTagNameTable
Select @v as StringWithoutTags
----------------------------------------------------------------------------------