title: How to modify a TSQL Replace table function for my case?
body: <p>I am using SSMS 2008 and I have the following Table function to take a text string and remove all metatags from Microsoft Word.  The tags are enclosed in "&lt;...>" and there can be any number of tags / record in one column.  </p>
  
  <p>I created this table function to update each row in this column. </p>
  
  <pre><code>alter function dbo.ufn_StripHTML
      (   @Input      varchar(8000),
          @Delimiter  char(1)
      )
  returns varchar(8000)
  as
  
  begin
  
      declare @Output varchar(8000)
      select  @Input = replace(replace(@input, '&lt;', @Delimiter), '&gt;', @Delimiter)
  
      select @Output = isnull(@Output, '') + s
      from    (    select   row_number() over (order by n.id asc) [i],
                   substring(@Delimiter + @Input + @Delimiter, n.id + 1, charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.id + 1) - n.id - 1) [s]
              from    [evolv_cs].[dbo].[progress_note] n
              where   n.id = charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.id) and
                   n.id &lt;= len(@Delimiter + @Input)
              ) d
      where i % 2 = 1
  
      return @Output
  
  end
  </code></pre>
  
  <p>This table function would work if [progress_note] had an "id" int column.  But it does not and I cannot modify this table either, by adding an int column.  So the problem is that I am trying to use this function on a temp table. </p>
  
  <p>So I tried creating a view based on this table and then adding a PK int column to it.  Because when I tried to create the view with this additional PK int column ("id"), it gave me an error:</p>
  
  <pre><code>Msg 156, Level 15, State 1, Line 1
  Incorrect syntax near the keyword 'identity'.
  </code></pre>
  
  <p>But ALTER VIEW does not support adding columns.  Is there another way to do this?  Here is my original temp table I am trying to modify:</p>
  
  <pre><code>select [progress_note].[note_text], [progress_note].[event_log_id]
  INTO #TEMP_PN
  from [evolv_cs].[dbo].[progress_note]
  group by [progress_note].[event_log_id], [progress_note].[note_text]
  </code></pre>
  
  <p>[note_text] is varchar(max) and event_log_id is uniqueidentifier.  So [note_text] contains a bunch of "&lt;" and ">" chars.  How can I modify this dbo.ufn_StripHTML function?</p>
  
  <p>Of course, if I try to replace [progress_note] table with #TEMP_PN in the table function, it will error cause it won't recognize it.  So how can I modify this function for my case?</p>