Início / dba / Perguntas / 313750
Accepted
Elite298
Asked: 2022-06-25 12:56:35 +0800 CST

Registros maiores que o timestamp de época usando apenas o operador LIKE

  • 772

Tenho a seguinte query até o momento e infelizmente não consigo usar operadores regexp ou maior que , só consigo usar a LIKEpalavra chave.

A coluna inteira está em uma string json, não posso usar json_valueou regexp porque estou no SQL Server, então estou preso ao uso do LIKE. É o SQL Server 2014 e json_valuenão tem suporte até 2016.

SELECT * FROM DataTableOne 
WHERE update_date LIKE '%1645290000%'

Eu gostaria de recuperar todos os registros onde o timestamp unix do epoch é maior que 1645290000 usando apenas a LIKEpalavra-chave SQL (ou mesmo entre 1645290000 e 9999999999 usando o LIKEoperador SQL).

Qualquer ajuda será muito apreciada, pois este é um caso único muito difícil em que estou limitado a usar apenas a palavra- LIKEchave.

Exemplo de tabela/dados abaixo:

CREATE TABLE DataTableOne (
    ID int,
    DATA varchar(MAX)
);

INSERT INTO DataTableOne (ID, DATA)
VALUES (1, '{"name":"Cole", "update_date":"2855290000"}'),
(2, '{"name":"Peter", "update_date":"1222290000"}') ;

Pode haver mil linhas com esse tipo de dados e as únicas que eu quero são aquelas em que o update_date é maior que 1645290000.

A execução da consulta na tabela acima que dei deve retornar apenas a primeira linha, pois a data_de_atualização de 2855290000 é realmente maior que 1645290000 numericamente.

sql-server sql-server-2014

2 respostas

  1. Acredito que seja melhor separar o problema em duas partes, localize o update_date e depois use ">" para filtrar:

    select * from DataTableOne
where cast ( substring( data
           , charindex('"update_date":', data)+15
           , len(data)- (charindex('"update_date":', data)+15) -1 )
      as bigint ) > 1645290000

    Observe que, se houver JSON malformado em sua tabela, a consulta falhará. Se for esse o caso, você pode querer encapsular a extração em uma função/procedimento com tratamento de erros.

    violino

    Ou, como TRY_CASTé implementado no SQL Server 2014, se o cast falhar, null é retornado, o que nunca satisfaz >, então podemos simplesmente fazer:

    select * from DataTableOne
where TRY_CAST ( substring( data
           , charindex('"update_date":', data)+15
           , len(data)- (charindex('"update_date":', data)+15) -1 )
         as bigint ) > 1645290000;

    violino atualizado

    • 9
  2. Best Answer

    Realisticamente, você não deve trabalhar diretamente em dados JSON em versões do SQL Server sem suporte explícito a JSON. Idealmente, a origem JSON seria transformada em um formato relacional durante a importação. Consultar os dados torna-se então fácil.

    Dito isto, se você realmente deve fazer o que diz, há várias opções.

    Uma é converter o JSON (simples) para XML e usar XQuery:

    SELECT
    DTO.*
FROM dbo.DataTableOne AS DTO
CROSS APPLY 
(
    SELECT 
        TRY_CONVERT(xml,
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(DTO.[DATA], 
                            '"name":', 'name='),
                        ', "update_date":', ' update_date='),
                    '{', '<r '),
                '}', '/>'))
) AS X (x)
WHERE
    1 = X.x.exist('r[1][@update_date ge 1645290000]');

    O [1]não é necessário, mas produz um plano de execução um pouco melhor no caso de haver apenas um update_date por linha.

    Também é quase possível usar LIKEexclusivamente, mas eu não recomendaria:

    SELECT * 
FROM dbo.DataTableOne AS DTO
WHERE 
    DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"164529[0-9][0-9][0-9][0-9]"%'
    OR DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"1645[3-9][0-9][0-9][0-9][0-9][0-9]"%'
    OR DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"164[6-9][0-9][0-9][0-9][0-9][0-9][0-9]"%'
    OR DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"16[5-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"%'
    OR DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"1[7-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"%'
    OR DTO.[DATA] COLLATE Latin1_General_BIN2 
        LIKE '%"update_date":"[2-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"%';

    db<> demonstração online de violino

    Se você entender a LIKElógica, poderá generalizá-la para outros valores. Isso deve começar se você estiver tentando resolver um quebra-cabeça ou definir algum tipo de questão.

    • 8

relate perguntas