Records greater than epoch timestamp using only LIKE operator

All we need is an easy explanation of the problem, so here it is.

I have the following query so far and unfortunately, I cannot use regexp or greater than operators, I can only use the LIKE keyword.

The whole column is in a json string, I can’t use json_value or regexp because I’m on SQL Server so I’m stuck with using LIKE. It’s SQL Server 2014 and json_value is not supported until 2016.

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

I would like to retrieve all records where the epoch unix timestamp is greater than 1645290000 using only the SQL LIKE keyword (or even between 1645290000 and 9999999999 using the SQL LIKE operator).

Any help will be much appreciated since this is a very tough unique case where I am limited to using only the LIKE keyword.

Sample table/data below:

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

There could be a thousand rows with this sort of data and the only ones I want are the ones where the update_date is greater than 1645290000.

Running the query on the above table I gave should only return the first row since the update_date of 2855290000 is indeed greater than 1645290000 numerically.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Realistically, you shouldn’t be directly working on JSON data in versions of SQL Server without explicit JSON support. Ideally, the JSON source would be transformed to a relational format during import. Querying the data then becomes easy.

That said, if you really must do as you say, there are a number of options.

One is to convert the (simple) JSON to XML, then use 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]');

The [1] isn’t necessary there, but produces a slightly nicer execution plan in the case there is only one update_date per row.

It is also just about possible to use LIKE exclusively, but I wouldn’t recommend it:

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 online demo

If you understand the LIKE logic there you will be able to generalise it for other values. That should get you started if you’re trying to solve a puzzle or set question of some kind.

Method 2

I believe it is best to separate the problem into two parts, locate the update_date and then use ">" to filter:

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

Note that if there are malformed JSON in your table the query will fail. If that is the case you may want to encapsulate the extraction in a function / procedure with error handling.

Fiddle

Or, since TRY_CAST is implemented in SQL Server 2014, if the cast fails, null is returned which never satisfies >, so we can simply do:

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

Updated Fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , , ,

Leave a Reply