主页 / dba / 问题 / 206481
Accepted
Andriy M
Asked: 2018-05-12 04:34:27 +0800 CST

使用“[ ]”通配符将 ]（右方括号）与 PATINDEX 匹配

  • 772

我正在用 T-SQL †编写自定义 JSON 解析器。

出于解析器的目的，我正在使用PATINDEX从标记列表中计算标记位置的函数。在我的例子中，标记都是单个字符，它们包括：

{ } [ ] : ,

通常，当我需要找到几个给定字符中的任何一个的（第一个）位置时，我会使用如下PATINDEX函数：

PATINDEX('%[abc]%', SourceString)

然后，该函数将为我提供aorb或的第一个位置c——以最先找到的为准——在SourceString.

现在我的问题似乎与]角色有关。一旦我在字符列表中指定它，例如这样：

PATINDEX('%[[]{}:,]%', SourceString)

我的预期模式显然被破坏了，因为该函数永远找不到匹配项。看起来我需要一种方法来逃避第一个]，以便PATINDEX将其视为查找字符之一而不是特殊符号。

我发现这个问题询问了类似的问题：

但是，在这种情况下，]不需要在括号中指定，因为它只是一个字符，并且可以在没有括号的情况下指定。确实使用转义的替代解决方案仅适用于LIKE而不适用于PATINDEX，因为它使用ESCAPE由前者支持而不是后者支持的子条款。

所以，我的问题是，有没有办法使用通配符来寻找 a ？]PATINDEX[ ]或者有没有办法使用其他 Transact-SQL 工具来模拟该功能？

附加信息

PATINDEX这是我需要与上述[…]模式一起使用的查询示例。这里的模式有效（尽管有点），因为它不包括]字符。我也需要它来工作]：

WITH
  data AS (SELECT CAST('{"f1":["v1","v2"],"f2":"v3"}' AS varchar(max)) AS ResponseJSON),
  parser AS
  (
    SELECT
      Level         = 1,
      OpenClose     = 1,
      P             = p.P,
      S             = SUBSTRING(d.ResponseJSON, 1, NULLIF(p.P, 0) - 1),
      C             = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0), 1),
      ResponseJSON  = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0) + 1, 999999)
    FROM
      data AS d
      CROSS APPLY (SELECT PATINDEX('%[[{]%', d.ResponseJSON)) AS p (P)
    UNION ALL
    SELECT
      Level         = ISNULL(d.OpenClose - 1, 0) + d.Level + ISNULL(oc.OpenClose, 0),
      OpenClose     = oc.OpenClose,
      P             = d.P + p.P,
      S             = SUBSTRING(d.ResponseJSON, 1, NULLIF(p.P, 0) - 1),
      C             = c.C,
      ResponseJSON  = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0) + 1, 999999)
    FROM
      parser AS d
      CROSS APPLY (SELECT PATINDEX('%[[{}:,]%' COLLATE Latin1_General_BIN2, d.ResponseJSON)) AS p (P)
      CROSS APPLY (SELECT SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0), 1)) AS c (C)
      CROSS APPLY (SELECT CASE WHEN c.C IN ('[', '{') THEN 1 WHEN c.C IN (']', '}') THEN 0 END) AS oc (OpenClose)
    WHERE 1=1
      AND p.P <> 0
  )
SELECT
  *
FROM
  parser
OPTION
  (MAXRECURSION 0)
;

我得到的输出是：

Level  OpenClose  P   S      C   ResponseJSON
-----  ---------  --  -----  --  ---------------------------
1      1          1          {   "f1":["v1","v2"],"f2":"v3"}
1      null       6   "f1"   :   ["v1","v2"],"f2":"v3"}
2      1          7          [   "v1","v2"],"f2":"v3"}
2      null       12  "v1"   ,   "v2"],"f2":"v3"}
2      null       18  "v2"]  ,   "f2":"v3"}
2      null       23  "f2"   :   "v3"}
2      0          28  "v3"   }

您可以看到]被包含S在其中一行中。该Level列表示嵌套的级别，表示括号和大括号的嵌套。如您所见，一旦级别变为2，它就永远不会回到1。如果我可以将其PATINDEX识别]为令牌，它将具有。

上述示例的预期输出为：

Level  OpenClose  P   S     C   ResponseJSON
-----  ---------  --  ----  --  ---------------------------
1      1          1         {   "f1":["v1","v2"],"f2":"v3"}
1      NULL       6   "f1"  :   ["v1","v2"],"f2":"v3"}
2      1          7         [   "v1","v2"],"f2":"v3"}
2      NULL       12  "v1"  ,   "v2"],"f2":"v3"}
2      0          17  "v2"  ]   ,"f2":"v3"}
1      NULL       18        ,   "f2":"v3"}
1      NULL       23  "f2"  :   "v3"}
1      0          28  "v3"  }

您可以在 db<>fiddle使用此查询。

†我们使用的是 SQL Server 2014，不太可能很快升级到原生支持 JSON 解析的版本。我可以编写一个应用程序来完成这项工作，但是解析的结果需要进一步处理，这意味着应用程序中的工作不仅仅是解析——这种工作会更容易，而且可能更有效，完成一个 T-SQL 脚本，如果我能将它直接应用到结果中就好了。

我不太可能使用 SQLCLR 作为这个问题的解决方案。但是，我不介意有人决定发布 SQLCLR 解决方案，因为这可能对其他人有用。

sql-server t-sql

5 个回答

  1. Best Answer

    我自己的解决方案（更多的是一种解决方法）包括指定一个字符范围，其中包括]并使用该范围以及[ ]通配符中的其他字符。我使用了一个基于 ASCII 表的范围。根据该表，该]角色位于以下街区：

    十六进制十进制字符
--- --- ----
…
5A 90 Z
5B 91 [
5C 92\
5D 93]
5E 94 ^
5F 95 _
…

    因此，我的范围采用 的形式[-^，即它包括四个字符：[、\、]、^。我还指定该模式使用二进制排序规则，以完全匹配 ASCII 范围。结果PATINDEX表达式最终看起来像这样：

    PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)

    这种方法的明显问题是模式开头的范围包括两个不需要的字符，\和^. 该解决方案对我有用，因为额外的字符永远不会出现在我需要解析的特定 JSON 字符串中。当然，一般来说这不可能是真的，所以我仍然对其他方法感兴趣，希望比我的更普遍。

    • 7

  2. 当我不得不进行大量的字符串拆分时，我可能对此有一个可怕的看法。

    如果您有一组已知的字符，请为它们制作一个表格。

    CREATE TABLE dbo.characters ( character CHAR(1) NOT NULL PRIMARY KEY CLUSTERED );

INSERT dbo.characters ( character )
SELECT *
FROM (
        SELECT '[' UNION ALL
        SELECT ']' UNION ALL
        SELECT '{' UNION ALL
        SELECT '}' UNION ALL
        SELECT ',' 
) AS x (v)

    然后将其与魔法CROSS APPLY一起使用CHARINDEX：

    SELECT TOP 1000 p.Id, p.Body, ca.*
FROM dbo.Posts AS p
CROSS APPLY (
    SELECT TOP 1 CHARINDEX(c.character, p.Body) AS first_things_first
    FROM dbo.characters AS c
    ORDER BY CHARINDEX(c.character, p.Body) ASC
) AS ca
WHERE ca.first_things_first > 0

    如果我遗漏了一些关于您需要做什么的明显内容，请让我知道。

    • 4

  3. 我过去曾见过一些方法，可以在搜索之前替换有问题的字符，然后再将其放回原处。

    在这种情况下，我们可以这样做：

    DECLARE @test NVARCHAR(MAX);
DECLARE @replacementcharacter CHAR(1) = CHAR(174);

SET @test = 'Test[]@String'

SELECT PATINDEX('%[[' + @replacementcharacter + '@]%', REPLACE(@test,']',@Replacementcharacter))

    此代码正确返回 5。我正在使用 ¬ 字符，因为它不太可能出现 - 如果没有您不会使用的 ASCII 字符，则此解决方案将不起作用。

    奇怪的是，您的问题的直接答案是否定的 - 我也无法让 PATINDEX 搜索“]”，但如果您替换它，则不需要。

    相同的示例，但没有变量用法：

    DECLARE @test NVARCHAR(MAX);

SET @test = 'Test[]@String'

SELECT PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))

    在您的代码中使用上述解决方案会产生您需要的结果：

    WITH
  data AS (SELECT CAST('{"f1":["v1","v2"],"f2":"v3"}' AS varchar(max)) AS ResponseJSON),
  parser AS
  (
    SELECT
      Level         = 1,
      OpenClose     = 1,
      P             = p.P,
      S             = SUBSTRING(d.ResponseJSON, 1, NULLIF(p.P, 0) - 1),
      C             = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0), 1),
      ResponseJSON  = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0) + 1, 999999)
    FROM
      data AS d
      CROSS APPLY (SELECT PATINDEX('%[[{'+ CHAR(174) + ']%', REPLACE(d.ResponseJSON,']',CHAR(174)))) AS p (P)
    UNION ALL
    SELECT
      Level         = ISNULL(d.OpenClose - 1, 0) + d.Level + ISNULL(oc.OpenClose, 0),
      OpenClose     = oc.OpenClose,
      P             = d.P + p.P,
      S             = SUBSTRING(d.ResponseJSON, 1, NULLIF(p.P, 0) - 1),
      C             = c.C,
      ResponseJSON  = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0) + 1, 999999)
    FROM
      parser AS d
      CROSS APPLY (SELECT PATINDEX('%[[{}:,'+ CHAR(174) + ']%' COLLATE Latin1_General_BIN2, REPLACE(d.ResponseJSON,']',CHAR(174)))) AS p (P)
      CROSS APPLY (SELECT SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0), 1)) AS c (C)
      CROSS APPLY (SELECT CASE WHEN c.C IN ('[', '{') THEN 1 WHEN c.C IN (']', '}') THEN 0 END) AS oc (OpenClose)
    WHERE 1=1
      AND p.P <> 0
  )
SELECT
  *
FROM
  parser
OPTION
  (MAXRECURSION 0)
;
    • 4

  4. 由于]仅在 中特殊，[...]您可以使用PATINDEX两次，移到. 评估和。如果一个结果为零，则取另一个。否则，取两个值中的较小者。][...]PATINDEX('%[[{}:,]%', SourceString)PATINDEX('%]%', SourceString)

    在您的示例中：

    WITH
  data AS (SELECT CAST('{"f1":["v1","v2"],"f2":"v3"}' AS varchar(max)) AS ResponseJSON),
  parser AS
  (
    SELECT
      Level         = 1,
      OpenClose     = 1,
      P             = p.P,
      S             = SUBSTRING(d.ResponseJSON, 1, NULLIF(p.P, 0) - 1),
      C             = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0), 1),
      ResponseJSON  = SUBSTRING(d.ResponseJSON, NULLIF(p.P, 0) + 1, 999999)
    FROM
      data AS d
      CROSS APPLY (SELECT PATINDEX('%[[{]%', d.ResponseJSON)) AS p (P)
    UNION ALL
    SELECT
      Level         = ISNULL(d.OpenClose - 1, 0) + d.Level + ISNULL(oc.OpenClose, 0),
      OpenClose     = oc.OpenClose,
      P             = d.P + ISNULL(p.P, 0),
      S             = SUBSTRING(d.ResponseJSON, 1, p.P - 1),
      C             = c.C,
      ResponseJSON  = SUBSTRING(d.ResponseJSON, p.P + 1, 999999)
    FROM
      parser AS d
      CROSS APPLY (VALUES (NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0))) AS p_ (a, b)
      CROSS APPLY (VALUES (CASE WHEN p_.a < p_.b OR p_.b IS NULL THEN p_.a ELSE p_.b END)) AS p (P)
      CROSS APPLY (SELECT SUBSTRING(d.ResponseJSON, p.P, 1)) AS c (C)
      CROSS APPLY (SELECT CASE WHEN c.C IN ('[', '{') THEN 1 WHEN c.C IN (']', '}') THEN 0 END) AS oc (OpenClose)
    WHERE 1=1
      AND p.P <> 0
  )
SELECT
  *
FROM
  parser
OPTION
  (MAXRECURSION 0)
;

    https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=66fba2218d8d7d310d5a682be143f6eb

    • 4

  5. 对于左“[”：

    PATINDEX('%[[]%',expression)

    对于正确的']'：

    PATINDEX('%]%',expression)
    • -4

相关问题