Replace characters in string without affecting names

Question

I am trying to remove certain characters in a string without removing them from peoples names.

I have tried using nested REPLACE statements, adding multiple CASE WHEN...THEN...ELSE statements but I am struggling to cover all bases with the code

In my table I have peoples names with prefixes and suffixes used to denote the status of the person in a number of instances.

This is an example of data in the table

ZZScott Buzzton SC

I need to remove the "ZZ" and "SC" text from here and just leave Scott Buzzton.

With the CASE statements I have been unable to get something that covers everything without layers of nesting

CASE
WHEN LEFT ( 'text' , 2 ) = 'ZZ' THEN STUFF ( 'text' , 1 , 2 , '' )
WHEN RIGHT ( 'text' , 2 ) = 'SC' THEN STUFF ( 'text' , LEN ( 'text' ) - 2 ) , 2 , '' )
ELSE 'text'
END

This only removes either "ZZ" or "SC" but not both. I was wondering if there was something simple I could do to remove them that doesn't involve altering the data in the table as that could break a number of reports and possibly our application

ADDITION: "ZZ" and "SC" are just 2 examples, there could be other texts used to denote status

Answer 1

apply is one method:

from t cross apply
     (values (case when t.col like 'ZZ%' then stuff(t.col, 1, 2, '') else col end)
     ) v1(col) cross apply
     (values (case when v1.col like ' SA%' then left(v1.col, len(v1.col) - 3) else v1.col end)
     ) v2(col)

Or, you could use brute force:

(case when t.col like 'ZZ% SA'
      then substring(col, 3, len(col) - 5)
      when t.col like '% SA'
      then left(col, len(col) - 3)
      when t.col like 'ZZ%'
      then stuff(col, 1, 2, '')
      else col
 end)