#07 SQL UDF to Clean Up HTML tags

When using SQL Reporting to report on text fields that are formatted with html tags this user defined function UDF (a modified copy of a post by William McEvoy) is helpful. It does not clean up all but does a decent job on most common tags. You can add offending tags and code to the replace special character’s list (ie.   below)

Create FUNCTION  [dbo].[ScrapeText]
(
  @string varchar(8000)
)
returns varchar(8000)

AS
BEGIN
---------------------------------------------------------------------------------------------------
-- Title:        ScrapeText
--
-- Date Created: April 4, 2006
--
-- Author:       William McEvoy
--
-- Description:  This function will attempt to remove rtf formatting from a string. This is
--               accomplished by concetenating all text contained between greater than and less
--               than signs within the formatted text.
--
-- Example:      <P>This text will be parsed and returned but not the P's</P>
---------------------------------------------------------------------------------------------------
-- Date Revised:  5/3/08
-- Author:       D Gorman
-- Reason:      Minor HTML tags & codes added to replace
---------------------------------------------------------------------------------------------------

declare @text  varchar(8000),
        @PenDown char(1),
        @char  char(1),
        @len   int,
        @count int

select  @count = 0,
        @len   = 0,
        @text  = ''

-- Add tokens
select @string = '>' + @string + '<'

-- Replace Special Characters
select @string = replace(@string,'&nbsp;',' ')
select @string = replace(@string,'<P>',char(13))
select @string = replace(@string,'</P>',char(13))
select @string = replace(@string,'&amp;',char(38))
select @string = replace(@string,'&copy;',char(169))

-- Parse out the formatting codes
select @len = len(@string)
while (@count <= @len)
begin
  select @char = substring(@string,@count,1)

  if (@char = '>')
     select @PenDown = 'Y'
  else
  if (@char = '<')
    select @PenDown = 'N'
  else
  if (@PenDown = 'Y')
    select @text = @text + @char

  select @count = @count + 1
end

RETURN @text
END

You would use this ‘upstream’ on the query/view or stored procedure used by the SQL Reporting report dataset definition.  In the report def you’d just reference the NewName field reference.

Select  dbo.ScrapeText(FieldX)  as NewName From ProjectCustomTabFields Where WBS1=’xxxxxx’