当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2008 - how can I replace different characters in a column without using cursor?

问题描述:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: