Databases
StackOverflow2013
Postsdbo
POHow to create a TSQL Replace table function?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to create a TSQL Replace table function?
    text
    copied!<p>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 "&lt;...>" and there can be any number of tags / record in one column. </p> <p>I created a scalar function based off this code below to update each row in this column. But this scalar function takes a super long time to complete. Would a table function version of this be faster? If so, how could I rewrite this function to make it table?</p> <pre><code>WHILE PATINDEX( '%[%]%', @str ) &gt; 0 SET @str = REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[%]%', @str ), 1 ), '' ) SELECT @str </code></pre> <p>This table function almost works. But it turns out that it doesn't work now. The problem is that I am trying to use this function on a temp table. And the original table does not have an int PK, and I cannot add a column to the original 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 ("n"), 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>I tried ur latest code and this is super fast!! However, I got the following error after it went thru 5700 rows:</p> <pre><code>Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. </code></pre> <p>Do u know what this is about?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload