Replace characters in string without affecting names

user_13712911 0
Viewed 52

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

tsql sql-server sql
2 Answers

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)
user_11440350

You could also try to put that to some view/function using 

SELECT 
    CASE WHEN RIGHT (col_1, 2) = 'SC' THEN STUFF(col_1, LEN(col_1) - 2), 2, '') ELSE col_1 END "col_1"
FROM (
    SELECT 
        CASE WHEN LEFT (col_1, 2) = 'ZZ' THEN STUFF(col_1, 1, 2, '') ELSE col_1 END "col_1"
    FROM myTable
)

(not tested)

user_51725130
Related