I agree that you'd most likely be better off using a single UPDATE for specific conditions whenever possible.

However, if that's not feasible at this time, other possibilites are:

(1) Add BEGIN...

Not sure what you mean.  It did remove 'rtf' from the string.  What more, specifically, do you want it to do?

As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs...

REPLACE(Note, 'rtf', '')

If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just...

That is a classic supertype/subtyte setup.  (Similar to sys.objects being a supertype in SQL Server to subtypes of table, procedure, etc.)

But you're missing the "supertype" table.   That is, you should...

As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs and FKs...

Rather than just going back to the first step, I would go to a later, isolated step in the job, and ultimately that step would go back to the first...

I agree that it's real tempting to...

Since you're only checking for an hour range, you can use BETWEEN.  It's just as accurate and easier to code and read.

To make changes easier -- and the code somewhat clearer to read -- I usually use this method for that:

WHERE DATEPART(HOUR, LOADDATE) BETWEEN 9 AND 17

After specifically a space, as you stated, that method's likely the best way.  But if you need to consider chars other than a space before the letter, then you'll need...

If you're working with SQL Server, look for SQL Server internals material.  One prominent author worth checking out there is Kalen Delaney and Paul White.  Lookup her/his stuff online  (since...

INSERT INTO [dbo].[TestNames2] ( [MBR_NM] ) VALUES
    ('von Richthofen, Manfred')

SELECT
    MBR_NM,
    RTRIM(LEFT(MBR_NM, comma_location - 1)) AS MBR_LastName,
 ...

Hmm, now that I think about it, you can probably get away with specifying a WHERE/JOIN condition on the client_id in the claim_line table, since SQL will likely still do...