Dopasowanie a] (zamykający nawias kwadratowy) do PATINDEX przy użyciu symbolu wieloznacznego „[]”

Piszę niestandardowy parser JSON w języku T-SQL ^† .

Na potrzeby mojego parsera używam PATINDEXfunkcji, która oblicza pozycję tokena na podstawie listy tokenów. Wszystkie tokeny w moim przypadku są pojedynczymi postaciami i obejmują one:

{} []:,

Zwykle, gdy muszę znaleźć (pierwszą) pozycję dowolnego z kilku podanych znaków, używam PATINDEXfunkcji w następujący sposób:

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

Funkcja będzie następnie dać mi pierwszą pozycję alub blub c- w zależności od tego co dzieje się znaleźć pierwszy - w SourceString.

Teraz problem w moim przypadku wydaje się być związany z ]postacią. Jak tylko podam to na liście postaci, np. Tak:

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

mój zamierzony wzorzec najwyraźniej zostaje zepsuty, ponieważ funkcja nigdy nie znajdzie dopasowania. Wygląda na to, że potrzebuję sposobu na ucieczkę od pierwszego, ]więc PATINDEXtraktuje to jako jedną z postaci, a nie specjalny symbol.

Znalazłem to pytanie z pytaniem o podobny problem:

• Potrzebujesz pomocy z operatorem LIKE i nawiasami kwadratowymi

Jednak w takim przypadku po ]prostu nie trzeba podawać w nawiasach, ponieważ jest to tylko jeden znak i można go podać bez nawiasów wokół nich. Alternatywne rozwiązanie, które korzysta ze znaku ucieczki, działa tylko dla, LIKEa nie dla PATINDEX, ponieważ wykorzystuje ESCAPEpodklucz, obsługiwany przez to pierwsze, a nie drugie.

Więc moje pytanie brzmi, czy jest jakiś sposób, aby szukać ]z PATINDEXużyciem [ ]symboli wieloznacznych? Czy istnieje sposób na emulowanie tej funkcjonalności przy użyciu innych narzędzi Transact-SQL?

Dodatkowe informacje

Oto przykład z zapytaniem gdzie muszę korzystać PATINDEXze […]wzoru jak powyżej. Wzór tutaj działa (choć nieco ), ponieważ nie zawiera ]znaku. Potrzebuję go również do pracy z ]:

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)
;

Otrzymuję wynik:

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"   }   

Możesz zobaczyć, że ]jest uwzględniony jako część Sw jednym z wierszy. LevelKolumna wskazuje poziom zagnieżdżenia, co oznacza, wspornik i szelki lęgowych. Jak widać, gdy poziom osiągnie 2, nigdy nie powróci do 1. Byłoby tak, gdybym mógł PATINDEXrozpoznać ]jako token.

Oczekiwany wynik dla powyższego przykładu to:

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"  }

Możesz grać z tym zapytaniem w db <> skrzypce .

_{^† Korzystamy z programu SQL Server 2014 i jest mało prawdopodobne, aby wkrótce zaktualizowano go do wersji obsługującej natywnie parsowanie JSON. Mógłbym napisać aplikację do wykonania zadania, ale wyniki analizy muszą być dalej przetwarzane, co oznacza więcej pracy w aplikacji niż tylko analizowanie - taki rodzaj pracy, który byłby o wiele łatwiejszy i prawdopodobnie bardziej wydajny, skrypt T-SQL, gdybym tylko mógł zastosować go bezpośrednio do wyników.}

_{Jest bardzo mało prawdopodobne, że mogę użyć SQLCLR jako rozwiązania tego problemu. Jednak nie mam nic przeciwko, jeśli ktoś zdecyduje się opublikować rozwiązanie SQLCLR, ponieważ może to być przydatne dla innych.}

Moje własne rozwiązanie, które jest raczej obejściem, polegało na określeniu zakresu znaków, który obejmował ]i używanie tego zakresu wraz z innymi znakami ze znaku [ ]wieloznacznego. Użyłem zakresu opartego na tabeli ASCII. Zgodnie z tą tabelą ]postać znajduje się w następującym sąsiedztwie:

Hex Dec Char
--- --- ----
…
5A 90 Z
5B 91 [
5C 92 \
5D 93]
5E 94 ^
5F 95 _
…

Mój zakres zatem miały formę [-^, to znaczy zawiera cztery znaki: [, \, ], ^. Podałem również, że wzorzec używa sortowania binarnego, aby dokładnie pasować do zakresu ASCII. Wynikowe PATINDEXwyrażenie wyglądało tak:

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

Oczywistym problemem związanym z tym podejściem jest to, że zakres na początku wzorca obejmuje dwie niepożądane postacie \oraz ^. Rozwiązanie działało dla mnie po prostu dlatego, że dodatkowe znaki nigdy nie mogły wystąpić w określonych ciągach JSON, które musiałem przeanalizować. Oczywiście nie może to być prawda, więc nadal interesują mnie inne metody, mam nadzieję, że są bardziej uniwersalne niż moje.

Mam prawdopodobnie okropne podejście do tego z tyłu, kiedy musiałem dużo rozłupywać struny.

Jeśli masz znany zestaw znaków, zrób ich tabelę.

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)

Następnie użyj tej magii CROSS APPLYwraz z 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

Jeśli brakuje mi czegoś oczywistego na temat tego, co musisz zrobić, daj mi znać.

W przeszłości widziałem podejścia, które zastępują obraźliwą postać przed wyszukiwaniem, a potem umieszczają ją z powrotem.

W tym przypadku możemy zrobić coś takiego:

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

SET @test = 'Test[]@String'

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

Ten kod poprawnie zwraca 5. Używam znaku ¬, ponieważ jest to mało prawdopodobne - jeśli nie ma znaków ASCII, których nie będziesz używać, to rozwiązanie nie będzie działać.

Co dziwne, bezpośrednia odpowiedź na twoje pytanie brzmiałaby: nie - nie mogę zmusić PATINDEX do wyszukania „]”, ale jeśli ją zastąpisz, nie musisz.

Ten sam przykład, ale bez użycia zmiennej:

DECLARE @test NVARCHAR(MAX);

SET @test = 'Test[]@String'

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

Zastosowanie powyższego rozwiązania w kodzie daje wymagane wyniki:

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)
;

Ponieważ ]jest on wyjątkowy [...], możesz go użyć PATINDEXdwa razy, wychodząc ]poza [...]. Oceń oba PATINDEX('%[[{}:,]%', SourceString)i PATINDEX('%]%', SourceString). Jeśli jeden wynik wynosi zero, weź drugi. W przeciwnym razie weź mniejszą z dwóch wartości.

W twoim przykładzie:

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

Dla lewego „[”:

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

Dla prawa „]”:

PATINDEX('%]%',expression)