SQL 替换字符串中的多个不同字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39231102/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Replace multiple different characters in string
提问by coblenski
I need to replace multiple characters in a string. The result can't contain any '&' or any commas.
我需要替换字符串中的多个字符。结果不能包含任何“&”或任何逗号。
I currently have:
我目前有：
REPLACE(T2.[ShipToCode],'&','and')
But how do you put multiple values in?
但是如何放入多个值呢？
Many thanks!
非常感谢！
回答by Siyual
You just need to daisy-chain them:
你只需要菊花链它们：
REPLACE(REPLACE(T2.[ShipToCode], '&', 'and'), ',', '')
回答by CLaFarge
One comment mentions "dozens of replace calls"... if removing dozens of single characters, you could also use Translate and a single Replace.
一条评论提到“数十次替换调用”……如果删除数十个单个字符，您还可以使用翻译和单个替换。
REPLACE(TRANSLATE(T2.[ShipToCode], '[];'',$@', '#######'), '#', '')
回答by Peter Schott
We used a function to do something similar that looped through the string, though this was mostly to remove characters that were not in the "@ValidCharacters" string. That was useful for removing anything that we didn't want - usually non-alphanumeric characters, though I think we also had space, quote, single quote and a handful of others in that string. It was really used to remove the non-printing characters that tended to sneak in at times so may not be perfect for your case, but may give you some ideas.
我们使用了一个函数来执行类似的循环字符串的操作，尽管这主要是为了删除不在“@ValidCharacters”字符串中的字符。这对于删除我们不想要的任何东西很有用 - 通常是非字母数字字符，尽管我认为我们在该字符串中还有空格、引号、单引号和一些其他字符。它确实用于删除有时会潜入的非打印字符，因此可能不适合您的情况，但可能会给您一些想法。
CREATE FUNCTION [dbo].[ufn_RemoveInvalidCharacters]
(@str VARCHAR(8000), @ValidCharacters VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @ValidCharacters + ']%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^' + @ValidCharacters +
']%',@str), 1) ,'')
RETURN @str
END
