有没有更聪明的方法来删除所有特殊字符而不是一系列约15个嵌套替换语句？
以下工作,但只处理三个字符(&符号,空白和句点).
select CustomerID,CustomerName,Replace(Replace(Replace(CustomerName,'&',''),' ','.','') as CustomerNameStripped from Customer
解决方法
一种灵活的方式;
ALTER FUNCTION [dbo].[fnRemovePatternFromString](@BUFFER VARCHAR(MAX),@PATTERN VARCHAR(128)) RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @POS INT = PATINDEX(@PATTERN,@BUFFER)
WHILE @POS > 0 BEGIN
SET @BUFFER = STUFF(@BUFFER,@POS,1,'')
SET @POS = PATINDEX(@PATTERN,@BUFFER)
END
RETURN @BUFFER
END
select dbo.fnRemovePatternFromString('cake & beer $3.99!?c','%[$&.!?]%')
(No column name)
cake beer 399c
