how can I replace different characters in a column without using cursor?

Question

Sample items in table1 

table1.productname 
Moshi Monsters 7-in-1 Accessory Pack - Poppet 
Mario vs. Donkey Kong Mini-Land Mayhem!

I would like to replace '- . !' from all the productname but using

select case
        when CHARINDEX ('-',[productname])>0 then REPLACE (ProductName ,'-',' ')
        when CHARINDEX ('!',[productname])>0 then REPLACE (ProductName ,'!','') 
        when CHARINDEX ('.',[productname])>0 then REPLACE (ProductName ,'.','') 
       else productname
       end as productname 
   from table1

seems to replace only -

output

Moshi Monsters 7 in 1 Accessory Pack   Poppet 
Mario vs. Donkey Kong Mini-Land Mayhem 

expected output 
Moshi Monsters 7 in 1 Accessory Pack   Poppet 
Mario vs Donkey Kong MiniLand Mayhem

How shall I approach for solution of this, I have multiple characters in the productname to replace such as in example and more and the column is around 5k big. actually I wanted to update the table1 with the changed name but wanted to see which are changed and how before I update. It seems all the requirement is not fulfilled with this kind of replace statement. Seems it could be done with multiple iterations in update but do not know how to use the iteration in update. How shall I process ahead?

The CASE statement only ever performs 1 of its WHEN/ELSE clauses, namely the first one it finds that applies. In your sample, since there is a '-' character in your product name, the CASE performs that particular WHEN clause and then stops. If there were no '-', only then would it check for the '!', and only if there is no '!' will it check for '.'. — Hellion
– Hellion, Commented Dec 13, 2012 at 15:09
@Hellion Thanks, so if I remove CASE from the statement that must be fine, right? I will try that now and see. like the one said by Marc B. — tough
– tough, Commented Dec 13, 2012 at 15:12

Marc B · Accepted Answer · 2012-12-13 15:04:34Z

3

You're always using the SAME source for your string replacements: the original ProductName field, which does not change. You need to chain the replacements:

REPLACE(REPLACE(REPLACE(ProductName, '.', ''), '!', ''), '-', '')

which gets hideously ugly very fast. You'd be better off doing this in your client.

answered Dec 13, 2012 at 15:04

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tough Over a year ago

Thanks, its fast and easier though looks ugly, It is what I was looking for.

Collectives™ on Stack Overflow

how can I replace different characters in a column without using cursor?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related