[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

TSQL Removing HTML tags from a field

Hello Experts,
I'm not sure if this is possible but i'm trying to read a text field into a temporary table and get rid of all html tags in it.

select substring(descriprion,1,200) into #temp  from myTable

I want to get rid of html tags that might exist in that column. the problem is I can not simply use Replace() and have it hardcoded since it may contain different HTML (e.g. <font color=red size=20> my description </font>)


I'm pretty much lookin for suggestions to achieve this

Thanks in advance
Rod
0
roddios
Asked:
roddios
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm not sure if this is possible but i'm trying to read a text field into a temporary table and get rid of all html tags in it.
TSQL is not really the best idea to do this job, I would rather use some other programming/scripting language, using the MSXML documents objects to get rid of the html tags...
0
 
g_johnsonCommented:
you can use CHARINDEX to find the position of "<", the CHARINDEX to find the position of ">" then remove everything in between.  this assumes that neither "<" nor ">" is a valid character other than as tags

0
 
roddiosAuthor Commented:
i would rather do it myself in vb.net but that's what i was asked to do.

Thanks g_johnson, didn't know those functions exist! (CHARINDEX and PATINDEX)

Here's the code if somebody's interested. it's a dumb code but enough for my purposes

CREATE FUNCTION dbo.removeHTMLTag
(
@myStr VARCHAR(2000) =''
)
RETURNS varchar(2000)
AS  
BEGIN
DECLARE @result VARCHAR(2000)
DECLARE @index INT
DECLARE @leftPos INT
DECLARE @rightPos INT
DECLARE @toBeRemovedStr VARCHAR(2000)
SET @index = patindex('%<[A-Z,/]%>%', @myStr)
WHILE (@index <> 0)
BEGIN
set @leftPos = @index
set @rightPos = charindex('>',subString(@myStr,@index, len(@myStr)))
set @toBeRemovedStr = substring(@myStr,@leftPos,@rightPos)
set @myStr = replace(@myStr,@toBeRemovedStr,'')
SET @index = patindex('%<[A-Z,/]%>%', @myStr)
END
select @result = @myStr
return ltrim(rtrim(@result))
END
0
 
g_johnsonCommented:
glad I could help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now