问题描述:

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?

网友答案:

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.

相关阅读:
Top