How to convert a Tsql scalar function into a table function?

I am using SSMS 2008 and I have the following scalar function to take a text string and remove all metatags from Microsoft Word. The tags are enclosed in "<...>" and there can be any number of tags / record in one column.

I created this scalar function to update each row in this column.

create function dbo.ufn_StripHTML ( @Input varchar(max), @Delimiter char(1) ) returns varchar(max) as begin declare @Output varchar(max) select @Input = replace(replace(@input, '<', @Delimiter), '>', @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 <= len(@Delimiter + @Input) ) d where i % 2 = 1 return @Output end 

This scalar 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.

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:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'identity'. 

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:

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] 

[note_text] is varchar(max) and event_log_id is uniqueidentifier. So [note_text] contains a bunch of "<" and ">" chars. How can I modify this function to make it a table function?

Of course, if I try to replace [progress_note] table with #TEMP_PN in this function, it will error because it won't recognize it. So how can I modify this function for my case?

Meanwhile, I developed a table function which accepts and outputs a table parameter. It does not error, but it does not return the parsed data I was hoping for either. What is missing?

CREATE TYPE dbo.MyTableType AS TABLE ( col1 int identity(1,1) NOT NULL, col2 varchar(max) NULL ) GO CREATE TABLE [dbo].[MyTable] ( [col1] [int] identity(1,1) NOT NULL, [col2] [varchar](max) NULL ) GO create PROC usp_AddRowsToMyTable @MyTableParam MyTableType READONLY, @Delimiter varchar(30) as INSERT INTO MyTable([col2]) SELECT [col2] FROM @MyTableParam --update MyTable --set col2 = replace(replace(MyTable.col2, '<', @Delimiter), '>', @Delimiter) select s, i, t from( select MyTableInput.col1 [i], replace(replace(MyTable.col2, '<', @Delimiter), '>', @Delimiter) as t, substring(@Delimiter + MyTableInput.col2 + @Delimiter, MyTable.col1 + 1, charindex(@Delimiter, @Delimiter + MyTableInput.col2 + @Delimiter, MyTable.col1 + 1) - MyTable.col1 - 1) [s] from MyTable inner join MyTable as MyTableInput on MyTable.col1 = MyTableInput.col1 where MyTable.col1 = CHARINDEX(@Delimiter, @Delimiter + MyTableInput.col2 + @Delimiter, MyTable.col1) and MyTable.col1 <= LEN(@Delimiter + MyTableInput.col2) ) d DECLARE @MyTable MyTableType INSERT INTO @MyTable(col2) VALUES ('<h><dsf>2000<h><dsf>'), ('<sd><dsf>2001'), ('2002<vnv><dsf>'), ('<gsd><dsf>2003<h><dsf>'), ('<eefs><dsf><h><dsf>2004<dfgd><dsf>') EXEC dbo.usp_AddRowsToMyTable @MyTableParam = @MyTable, @Delimiter = '|'