删除垃圾字符的任何方法都比使用太多替换功能

Question

我有

下的东西

declare @t table(city varchar(100))
insert into @t values('ab,c:d/tyu'),('frd/hj&ergg:')
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,',',' '),'"',''),':',''),'-',''),'/',''),'',''),'&','')
from @t
output
----------
ab cdtyu
frdhjergg

可以弄清楚的是，由于太多的垃圾字符，我正在使用许多替换函数。

有一种更好，优雅的方法来实现这一目标吗？

Answer 1

我将其作为第二个答案，因为它是一种全新的，基于集合的方法

与我的其他答案一样，我使用可维护的替换值的表：

CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
                           ,ReplWith VARCHAR(1) NOT NULL
                           ,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
                               ,('"','',2)
                               ,(':','',3)
                               ,('-','',4)
                               ,('/','',5)
                               ,('','',6)
                               ,('&','',7);
GO
declare @t table(city varchar(100))
insert into @t values('ab,c:d/tyu'),('frd/hj&ergg:');

- 查询将使用 tally 表即时生成一个运行号。然后，SUBSTRING用于选择每个字符，并在替换值的表中找到它。如果没有发现，则使用该值，否则替换值。最后，使用 FOR XML

重新结识分离的字符

WITH Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values) --approx 2500 values. Need more? Use CROSS JOIN
SELECT t.city 
,(
    SELECT ISNULL(rv.ReplWith,SUBSTRING(t2.city,Nmbr,1))
    FROM @t AS t2
    CROSS APPLY(SELECT TOP (LEN(t2.City)) Nmbr FROM Tally) AS A
    LEFT JOIN ReplaceValues AS rv ON rv.FindChar=SUBSTRING(t2.city,Nmbr,1)
    WHERE t.city=t2.city
    ORDER BY A.Nmbr
    FOR XML PATH(''),TYPE
).value(N'.','nvarchar(max)') ReBuilt
FROM @t AS t
GO
--Clean-Up
DROP TABLE ReplaceValues;

Answer 2

古怪更新的罕见情况之一是个好主意。不要期望表现最好，但是循环，递归CTE等的任何其他方法也都会有RBAR ...

这种方法的优点是：您可以在表中维护替换值。您甚至可以用其他更长的字符串替换更长的字符串，这可能是真正的魔术：通过定义顺序，您甚至可以进行级联替换...

CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
                           ,ReplWith VARCHAR(1) NOT NULL
                           ,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
                               ,('"','',2)
                               ,(':','',3)
                               ,('-','',4)
                               ,('/','',5)
                               ,('','',6)
                               ,('&','',7);
GO

- 通常，您不能使用 Quirky Update 封印，但是可以将其包装在标量函数中：

CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    --Quirky Update: One of the rare situations where this is a good idea 
    SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,rv.FindChar,rv.ReplWith)
    FROM ReplaceValues AS rv
    ORDER BY rv.SortOrder;
    RETURN @ReplaceTarget;
END
GO

- 您的桌子

declare @t table(city varchar(100))
insert into @t values('ab,c:d/tyu'),('frd/hj&ergg:');

--...魔术在这里：

SELECT t.city
      ,dbo.MultiReplace(t.city) AS CleanedCity
FROM @t AS t
GO

- 清理

DROP FUNCTION dbo.MultiReplace;
DROP TABLE ReplaceValues;

结果

city            CleanedCity
ab,c:d/tyu     ab cdtyu
frd/hj&ergg:   frdhjergg

Answer 3

使用函数

尝试以下方法：

Create Function [dbo].[Removejunkcharacter]
 (@CityTempValue varchar(100))
 Returns varchar(100)
 AS
 Begin
    Declare @value as varchar(100)
    Set @value = '%[^a-z]%'
    While PatIndex(@value, @CityTempValue) > 0
        Set @CityTempValue = Stuff(@CityTempValue, PatIndex(@value, @CityTempValue), 1, '')
    Return @CityTempValue
End

执行查询：

Declare @t table(city varchar(100))
Insert into @t values('ab,c:d/tyu'),('frd/hj&ergg:')
Select t1.city AS Input,
    [dbo].[Removejunkcharacter](t1.city) AS Result
    FROM @t AS t1;

结果：

   Input            Result
ab,c:d/tyu        abcdtyu
frd/hj&ergg:      frdhjergg

Answer 4

尝试以下方法：

declare @t table(id int, value varchar(15))
insert @t values(1, 'ab,c:d/tyu')
insert @t values(2, 'frd/hj&ergg')
;with t1 as
(
   select value i, id from @t
   union all
   select cast(replace(i, substring(i, PatIndex('%[^a-z0-9]%', i), 1), '') 
   as varchar(15)), id
   from t1
   where PatIndex('%[^a-z0-9]%', i) > 0
)
select t2.value as [Input], t1.i as [Result] from t1
join @t t2
on t1.id = t2.id
where PatIndex('%[^a-z0-9]%', i) = 0
option (maxrecursion 0)

Answer 5

这是基于shnugo的ReplaceValues表和递归CTE的另一个相当简单的解决方案：

shnugo的桌子：

CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
                           ,ReplWith VARCHAR(1) NOT NULL
                           ,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
                               ,('"','',2)
                               ,(':','',3)
                               ,('-','',4)
                               ,('/','',5)
                               ,('','',6)
                               ,('&','',7);
GO

样本数据：

declare @t table(city varchar(100))
insert into @t values('ab,c:d/tyu'),('frd/hj&ergg:')

查询：

;WITH CTE AS
(
    SELECT city, 1 as level
    FROM @T 
    UNION ALL
    SELECT CAST(Replace(city,FindChar,ReplWith) as varchar(100)), level + 1
    FROM CTE 
    INNER JOIN ReplaceValues ON level = SortOrder
)
select TOP 1 WITH TIES City
from CTE
ORDER BY level DESC

结果：

City
1   frdhjergg
2   ab cdtyu