Início / dba / Perguntas / 206481
Accepted
Andriy M
Asked: 2018-05-12 04:34:27 +0800 CST

Combinando um ] (colchete de fechamento) com PATINDEX usando o curinga "[ ]"

  • 772

Estou escrevendo um analisador JSON personalizado em T-SQL † .

Para fins do meu analisador, estou usando a PATINDEXfunção que calcula a posição de um token de uma lista de tokens. Os tokens no meu caso são todos caracteres únicos e incluem estes:

{ } [ ] : ,

Normalmente, quando preciso encontrar a (primeira) posição de qualquer um dos vários caracteres fornecidos, uso a PATINDEXfunção assim:

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

A função então me dará a primeira posição de aou bou c– o que for encontrado primeiro – em SourceString.

Agora o problema no meu caso parece estar relacionado com o ]personagem. Assim que eu especificar na lista de caracteres, por exemplo, assim:

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

meu padrão pretendido aparentemente se quebra, porque a função nunca encontra uma correspondência. Parece que preciso de uma maneira de escapar do primeiro ]para que PATINDEXo trate como um dos caracteres de pesquisa em vez de um símbolo especial.

Eu encontrei esta pergunta perguntando sobre um problema semelhante:

No entanto, nesse caso o ]simplesmente não precisa ser especificado entre colchetes, porque é apenas um caractere e pode ser especificado sem colchetes ao redor deles. A solução alternativa, que usa escape, funciona apenas para LIKEe não para PATINDEX, porque usa uma ESCAPEsubcláusula, apoiada pela primeira e não pela segunda.

Então, minha pergunta é, existe alguma maneira de procurar um ]usando PATINDEXo [ ]curinga? Ou existe uma maneira de emular essa funcionalidade usando outras ferramentas Transact-SQL?

informação adicional

Aqui está um exemplo de uma consulta onde eu preciso usar PATINDEXcom o […]padrão acima. O padrão aqui funciona (embora um pouco ) porque não inclui o ]personagem. Eu preciso dele para trabalhar com ]também:

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

A saída que recebo é:

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

Você pode ver que o ]está incluído como parte de Suma das linhas. A Levelcoluna indica o nível de aninhamento, significando aninhamento de colchetes e chaves. Como você pode ver, uma vez que o nível se torna 2, ele nunca retorna a 1. Teria sido se eu pudesse fazer PATINDEXreconhecer ]como um token.

A saída esperada para o exemplo acima é:

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

Você pode brincar com esta consulta em db<>fiddle .

† Estamos usando o SQL Server 2014 e provavelmente não atualizaremos em breve para uma versão que suporte a análise JSON nativamente. Eu poderia escrever um aplicativo para fazer o trabalho, mas os resultados da análise precisam ser processados ​​posteriormente, o que implica mais trabalho no aplicativo do que apenas a análise - o tipo de trabalho que seria muito mais fácil e provavelmente mais eficiente feito com um script T-SQL, se eu pudesse aplicá-lo diretamente aos resultados.

É muito improvável que eu possa usar o SQLCLR como solução para esse problema. No entanto, não me importo se alguém decidir postar uma solução SQLCLR, pois isso pode ser útil para outras pessoas.

sql-server t-sql

5 respostas

  1. Best Answer

    Minha própria solução, que é mais uma solução alternativa, consistia em especificar um intervalo de caracteres que incluía o ]e usar esse intervalo junto com os outros caracteres no [ ]curinga. Eu usei um intervalo baseado na tabela ASCII. De acordo com essa tabela, o ]personagem está localizado no seguinte bairro:

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

    Meu intervalo, portanto, assumiu a forma de [-^, ou seja, incluiu quatro caracteres: [, \, ], ^. Também especifiquei que o padrão usa um agrupamento binário, para corresponder exatamente ao intervalo ASCII. A expressão resultante PATINDEXficou assim:

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

    O problema óbvio com essa abordagem é que o intervalo no início do padrão inclui dois caracteres indesejados \e ^. A solução funcionou para mim simplesmente porque os caracteres extras nunca poderiam ocorrer nas strings JSON específicas que eu precisava analisar. Naturalmente, isso não pode ser verdade em geral, então ainda estou interessado em outros métodos, espero que mais universais que o meu.

    • 7

  2. Eu tenho uma opinião provavelmente terrível sobre isso quando eu tinha que fazer muita divisão de cordas.

    Se você tiver um conjunto conhecido de caracteres, faça uma tabela deles.

    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)

    Então use essa mágica CROSS APPLYjunto com 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

    Se estou perdendo algo óbvio sobre o que você precisa fazer, deixe-me saber.

    • 4

  3. Eu vi abordagens no passado para substituir o personagem ofensivo antes de pesquisar e colocá-lo de volta depois.

    Neste caso poderíamos fazer algo como:

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

SET @test = 'Test[]@String'

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

    Este código retorna corretamente 5. Estou usando o caractere ¬, pois é improvável que apareça - se não houver caracteres ASCII que você não usará, esta solução não funcionará.

    Curiosamente, porém, a resposta direta à sua pergunta seria não - também não consigo fazer com que o PATINDEX procure por ']', mas se você substituí-lo, não precisa.

    Mesmo exemplo, mas sem o uso da variável:

    DECLARE @test NVARCHAR(MAX);

SET @test = 'Test[]@String'

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

    Usar a solução acima em seu código produz os resultados necessários:

    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. Como ]só é especial no [...], você pode usar PATINDEXduas vezes, saindo ]do [...]. Avalie tanto PATINDEX('%[[{}:,]%', SourceString)e PATINDEX('%]%', SourceString). Se um resultado for zero, pegue o outro. Caso contrário, pegue o menor dos dois valores.

    No seu exemplo:

    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. Para uma esquerda '[':

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

    Para um direito ']':

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

relate perguntas