主页 / dba / 问题 / 313750
Accepted
Elite298
Asked: 2022-06-25 12:56:35 +0800 CST

仅使用 LIKE 运算符的大于纪元时间戳的记录

  • 772

到目前为止，我有以下查询，不幸的是，我不能使用正则表达式或大于运算符，我只能使用LIKE关键字。

整个列都在一个 json 字符串中，我不能使用json_value或正则表达式，因为我在 SQL Server 上，所以我坚持使用LIKE. 它是 SQL Server 2014，json_value直到 2016 年才受支持。

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

我想仅使用 SQLLIKE关键字（甚至使用 SQLLIKE运算符在 1645290000 和 9999999999 之间）检索纪元 unix 时间戳大于 1645290000 的所有记录。

任何帮助将不胜感激，因为这是一个非常艰难的独特案例，我仅限于使用LIKE关键字。

示例表/数据如下：

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"}') ;

这种数据可能有一千行，我唯一想要的是 update_date 大于 1645290000 的行。

在我给出的上表上运行查询应该只返回第一行，因为 2855290000 的update_date在数值上确实大于 1645290000。

sql-server sql-server-2014

2 个回答

  1. 我相信最好把问题分成两部分，定位到update_date，然后用">"过滤：

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

    请注意，如果您的表中存在格式错误的 JSON，则查询将失败。如果是这种情况，您可能希望将提取封装在具有错误处理的函数/过程中。

    小提琴

    或者，由于TRY_CAST是在 SQL Server 2014 中实现的，如果转换失败，则返回 null ，它永远不会满足>，所以我们可以简单地这样做：

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

    更新小提琴

    • 9
  2. Best Answer

    实际上，您不应该在没有明确 JSON 支持的 SQL Server 版本中直接处理 JSON 数据。理想情况下，JSON 源将在导入期间转换为关系格式。然后查询数据变得容易。

    也就是说，如果你真的必须按照你说的去做，有很多选择。

    一种是将（简单）JSON 转换为 XML，然后使用 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]');

    那里没有必要，但在每行只有一个update_date[1]的情况下会产生更好的执行计划。

    它也几乎可以LIKE专门使用，但我不推荐它：

    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<>fiddle在线演示

    如果您了解那里的LIKE逻辑，您将能够将其推广到其他值。如果您要尝试解决难题或设置某种问题，那应该可以帮助您入门。

    • 8

相关问题