对T中的字符范围使用通配符

Question

我目前正在使用REPLACE替换下面的字符，这些字符可以在客户名称中找到。然而，这样做是非常乏味的。

有没有人知道是否有办法使用列表格式(例如，这样的通配符)：LIKE ['.',',']，而不是每次编写替换？

REPLACE(REPLACE(REPLACE(REPLACE(dname,'.',''),'`',''),'''',''),'  ',' ')))

Answer 1

我们不知道版本，但如果您只是2016+，TRANSLATE可能会在这里很好地工作：

DECLARE @ReplaceChars varchar(50) = '.''`(){}[]!"£$%^&*-=_+';

SELECT REPLACE(REPLACE(TRANSLATE(YourColumn, @ReplaceChars, REPLICATE(LEFT(@ReplaceChars, 1), LEN(@ReplaceChars)),LEFT(@ReplaceChars,1),''),'  ',' ')
FROM ...

您仍然需要在最左边的字符和双空格中使用REPLACE。

Answer 2

对于这种类型的事情，我将使用PatExclude8K。它不是标量，100%基于集和讨厌的快速。

要从此字符串中删除非字母数字，请执行以下操作：

SELECT f.* FROM dbo.PatExclude8K('ABC123!!!   ???','[^A-Z0-9]') AS f;

返回： ABC123

只返回值表中的数字：

DECLARE @table TABLE (someid INT IDENTITY, somestring VARCHAR(100));
INSERT @table (somestring) 
SELECT TOP (10) NEWID() FROM sys.all_columns;

SELECT t.someid, t.somestring, pe.NewString
FROM  @table AS t
CROSS APPLY dbo.PatExclude8K(t.somestring,'[^0-9]') AS pe

返回：

someid      somestring                                NewString
----------- ----------------------------------------- ---------------------------
1           2FEF1D43-1A85-456D-BF9E-B329AD64A980      2143185456932964980
2           EB73205F-84C8-407E-8D4F-66FAFD1F556B      7320584840784661556
3           5BEA68B1-783B-4F57-A24D-CF110ADECFEA      568178345724110
4           FC7466E3-5CB8-4DDD-B7F0-30A539DF7C02      746635847030539702
5           800E3AC3-257F-4FF5-B7EE-E6B9268B5608      80033257457692685608
6           A1C33269-48EC-4100-A691-0EA9F2C55E21      1332694841006910925521
7           9C19F844-FE71-40BE-BFFF-276FE344B171      9198447140276344171
8           08529640-E77E-44AD-93A9-E69CE92AF1BD      08529640774493969921
9           FBADC1AE-ED96-4A0E-B106-C6C34E34A612      1964010663434612
10          7E52CFC5-025E-431B-99C1-589E957726B5      75250254319915899577265

Answer 3

如果您正在使用客户名称，那么您实际上应该使用NVARCHAR而不是VARCHAR，因为您不能保证名称中只有美国英语字符(即( "A“- "Z")加上一些不同的重音字符(我假设您使用的是默认的排序规则，即*Latin1_General*，它反过来对VARCHAR数据使用代码页1252 )。

也就是说，有很多字符在名字中是有效的(通常是字母，但也有连字符和逗号)，还有很多字符是无效的。尝试指定任何一个组，即使是字符类(即[...])中的一系列字符，每次新的字符出现时都需要进行更新。

处理这一问题的一种简单方法是使用正则表达式(即RegEx，而不是，LIKE和PATINDEX函数的[...]通配符不是正则表达式，不管有多少人这样引用它)。Server本机不支持RegEx，但您可以通过SQLCLR获得该功能，该功能适用于从2005年开始的所有版本(包括Linux上的Server )和Azure SQL数据库托管实例；它仅在常规Azure SQL数据库和AWS Server中不可用(从2017年版本开始)。获得RegEx的一个简单方法是下载和安装SQL#，这是我创建的SQLCLR库(大多数RegEx函数都在免费版本中，包括下面使用的内容)。

正则表达式不仅处理复杂的模式(比我们在这里处理的复杂得多)，而且还允许我们指定Unicode“类别”。对于这个特殊的情况，我们只需要使用“字母”类别，它包括大写字母、小写字母和其他形式的字母。单独使用这个类别也会删除连字符和逗号，而且由于我们可能不想这样做(因为它们在名称中是有效的)，我们可以很容易地将它们添加回。

我们将使用的表达式是：[^\p{L}, -]。这一模式被解读为：

• [^...] =查找与此列表中的字符不匹配的任何单个字符
• \p{L} =匹配被归类为“字母”的任何字符(在任何语言中，这就是为什么这只是工作)
• , - =匹配逗号、空格和连字符。由于字符类中使用连字符来表示范围，因此如果要用作文字连字符，则它们需要是第一个字符或最后一个字符。

这就引出了下面的例子：

SELECT SQL#.RegEx_Replace4k(
            N'a    .`     ''b$c   d  ef-ghi,jr. ꓤ ඖ  ל ؼ ញ z', -- string to modify
            N'[^\p{L}, -]',   -- regular expression (pattern)
            N'',              -- replacement
            -1,               -- number of occurrences to replace (-1 = unlimited)
            1,                -- character position to start at
            NULL              -- RegEx options (such as case-insensitive, multi-line, etc)
       );

 --a         bc   d  ef-ghi,jr ꓤ ඖ  ל ؼ ញ z

当然，这仍然留给我们一个没有其他答案(正确)解决的问题:将多个空格转换为一个单独的空间。

在这个问题中，您设置了一个REPLACE来将两个空格转换为单个空格。只有在只有两个空格的情况下，这才能工作。如果有三个或更多的空格，那么它只会转换每组两个，这仍然会给您留下多个空格。例如：

SELECT REPLACE(N'a   b', N'  ', N' ') AS [3 spaces],
       REPLACE(N'a    b', N'  ', N' ') AS [4 spaces],
       REPLACE(N'a     b', N'  ', N' ') AS [5 spaces];

/*
3 spaces    4 spaces    5 spaces
a  b        a  b        a   b
*/

如您所见，"3“和"4”空间测试都留下了两个空格，而"5“空间测试留下了三个空格。

以下是RegEx非常适合的另一种操作类型。您可以指定一个模式来匹配“两个或多个空格”，然后它将处理任意数量的空格，并将任何匹配的空间替换为单个空格，无论是2、3还是27个空格。我们可以使用\s{2,}的模式(意为“两个或多个空白字符”)，也可以使用\s\s+的模式(意为“空白字符后面跟着一个或多个空白字符”)。

例如，如果我们从前面的RegEx测试的输出开始，我们可以执行以下操作：

SELECT SQL#.RegEx_Replace4k(
           N'a         bc   d  ef-ghi,jr ꓤ ඖ  ל ؼ ញ z', N'\s{2,}',
           N' ',
           -1, 1, NULL);

--a bc d ef-ghi,jr ꓤ ඖ ל ؼ ញ z