主页 / dba / 问题 / 139106
Accepted
pix1985
Asked: 2016-05-21 03:02:16 +0800 CST

标点符号是否有 T-SQL 等效项，因为 [0-9] 用于数字，[az] 用于字母？

  • 772

是否有与[0-9]and[a-z]模式等效的 T-SQL 可以让我从包含标点符号的列中提取值？

例如：

Create Table #Test
(
Value   VarChar(10)
) 
Insert Into #Test
Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789')

Select      *
From        #Test
Where       Value like '[0-9][0-9][0-9][a-z]'

这将返回前 3 个字符是 0 到 9 之间的数字，最后一个字符是 a 和 z 之间的字母的值，因此会返回类似123aand456b但不会返回12ABC.

我想知道标点符号[0-9]是否与数字和[a-z]字母相同，以便返回AB!23和C?D789？

如果我可以使用正则表达式，我可能会使用该表达式^[a-zA-Z0-9]*$来匹配字符串中的字母数字字符。

Where       Value like '^[a-zA-Z0-9]*$'

是否有对应的 SQL？

我知道这种事情可以在 RegEx 中完成，但我在 T-SQL 中需要它，我无法将任何自定义程序集加载到此服务器上，因此无法使用正则表达式。

真正的列是varchar(200)。排序规则是 Latin1_General_CI_AS。我正在使用 SQL Server 2012 标准版。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    获得精确解决方案的最大困难是准确定义要包含哪些字符（或排除哪些字符，无论哪个方向对操作更有意义）。意义：

    • 我们在谈论VARCHAR/ ASCII 数据还是NVARCHAR/ Unicode 数据？ASCII 数据的标点符号列表取决于代码页，而代码页又取决于排序规则。（在这个问题中，我们正在处理 ASCII 数据）。
    • 我们是在处理区分大小写还是不区分大小写的搜索？
    • 该列设置为什么排序规则？排序规则将告诉我们代码页和区分大小写。（在我们正在处理的这个问题中Latin1_General_CI_AS）
    • 术语“标点符号”是指标准标点符号（例如，、、、.等,）;还是:指非字母数字字符？
    • 是否包含空格字符？
    • 是否包含控制字符？
    • 诸如¢, £,¥等货币符号呢？
    • ©和之类的符号™呢？
    • 哪些字符被认为是“alpha”？是否包含非英文字符，例如Â, É, Ñ, ß, Þ？
    • 由于此问题涉及英国键盘（请参阅此问题的讨论），那么Æ/æ字符呢？

    为了帮助更清楚地了解预期行为，以下查询将显示 Latin1 字符集的所有 256 个字符（即代码页 1252）以及@Shaneis提出的解决方案的两种变体如何运行。第一个字段（标记为Latin1_General_CI_AS）显示了LIKE@Shaneis 提出的子句（在撰写本文时），第二个字段（标记为Latin1_General_100_BIN2）显示了一个修改，其中我覆盖了排序规则以指定一个二进制（即排序规则以_BIN2;结尾）_BIN排序规则已弃用，因此如果您可以访问版本，请不要使用它们_BIN2）这意味着我还需要在A-Z范围中添加以过滤掉大写字母，因为当前排序规则不区分大小写：

    ;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS [Decimal]
  FROM   [master].[sys].[all_objects]
)
SELECT nm.[Decimal],
       CHAR(nm.[Decimal]) AS [Character],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9]%'
               THEN 'x' ELSE '' END AS [Latin1_General_CI_AS],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9A-Z]%' COLLATE Latin1_General_100_BIN2
               THEN 'x' ELSE '' END AS [Latin1_General_100_BIN2]
FROM   nums nm;

    更新

    应该提到的是，如果一个人真的在寻找被归类为“标点符号”（而不是“货币符号”、“数学符号”等）的字符，并且如果一个人不被禁止使用 SQLCLR/加载自定义程序集（SQLCLR 是在 SQL Server 2005 中引入的，我还没有遇到不允许它的充分SAFE理由，特别是因为 Azure SQL 数据库 V12 支持程序集），那么您可以使用正则表达式，但不是大多数人的原因会猜。

    \w您可以指定要过滤的字符的 Unicode 类别，而不是使用正则表达式来构建功能更强大的字符范围，或者甚至不使用类似的东西（意思是任何“单词”字符），并且有几个定义的类别：

    https://www.regular-expressions.info/unicode.html#category

    您甚至可以指定要过滤的 Unicode 块，例如“InBengali”或“InDingbats”或“InOptical_Character_Recognition”等：

    https://www.regular-expressions.info/unicode.html#block

    有许多为 SQL Server 创建 RegEx 函数的示例（尽管大多数示例不遵循 SQLCLR 最佳实践），或者您可以下载SQL#库的免费版本（我创建的），并使用标量RegEx_IsMatch函数，如下所示：

    SQL#.RegEx_IsMatch(Unicode-String-Expression, N'\p{P}', 1, NULL)

    该\p{P}表达式表示\p= Unicode 类别和{P}= 所有标点符号（与特定类型的标点符号相反，例如“连接符标点符号”）。而且，“标点符号”类别包括所有语言的所有标点符号！您可以通过以下链接在 Unicode.org 网站上查看完整列表（该类别中目前有 717 个代码点）：

    http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AGeneral_Category%3DPunctuation%3A%5D

    上面显示的测试查询的更新版本，包括另一个使用SQL#.RegEx_IsMatch的字段\p{P}，以及代码页 1252（即 Latin1_General）的所有 256 个字符的所有 3 个测试的结果已发布在 PasteBin.com 上：

    用于过滤字符类型的 T-SQL 查询和结果

    更新
    相关讨论中提到了以下内容：

    您对重音字符提出了很好的观点，因为它们是来自世界各地的酒店名称，名称中会有重音字符，对于我的问题，我想将它们归类为有效的字母字符。

    在这种情况下：

    1. 有 11 个非英语字符包含在 Latin1 字符集/代码页中，与a-z范围不匹配。他们是：ð Ð Þ þ œ Œ š Š ž Ž Ÿ。这些需要添加到通配符中，虽然目前没有必要，但添加它不会有什么坏处，A-Z这样该模式在区分大小写的排序规则上也能正常工作。最终结果是：
      LIKE '%[^a-zA-Z0-9ðÐÞþœŒšŠžŽŸ]%'

    2. 考虑到这些数据可以包括“来自世界各地的酒店名称”，我强烈建议将列的数据类型更改为，NVARCHAR以便您可以存储来自所有语言的所有字符。将其保留为VARCHAR最终丢失数据的风险很高，因为您只能表示基于拉丁语的语言，甚至不能完全表示那些提供额外拉丁相关字符的六个补充 Unicode 类别的语言。

    • 12

  2. 我可能会过度简化这一点，但是，如果我们说删除字母数字值后只剩下标点符号，那么下面将搜索其中包含非字母数字字符的字符串。

    Create Table #Test
(
Value   VarChar(10)
) 
Insert Into #Test
Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789')

-- Original
Select      *
From        #Test
Where       Value like '[0-9][0-9][0-9][a-z]'

-- Non Alpha-numeric
SELECT * FROM #Test WHERE Value LIKE '%[^a-z0-9]%';

DROP TABLE #Test;
    • 5

相关问题