5

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.

1
  • Its in a json string, I posted the schema/sample data in table
    – Elite298
    Jun 26 at 15:44

2 Answers 2

8

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.

1
  • You're right and let me spend some understanding and trying
    – Elite298
    Jun 25 at 12:28
9

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

5
  • Perhaps I'm a bit confused - but where is the 15 coming from? To what does it correspond? I was pursuing this from a slightly different tack - i.e. you can't have EPOCHs over 2^(32 - 1). What's so special about 15? Jun 25 at 17:13
  • The length of "update_date": is 14, so it's to pick first position after that
    – Lennart
    Jun 25 at 17:20
  • Yes - I see now! It doesn't matter how long the string is - your SQL will pick it up no matter what. Mine will eliminate anything that's not a valid EPOCH - but your solution is easily changed to do that also. Plus yours doesn't have the ugly calls to REVERSE() and no arbitrary 5 character "hop" to obtain the end of the string! I think this should have been the accepted solution - and thanks for having taken the time to explain the logic - upvoted! Jun 26 at 7:31
  • One drawback with my solution is that it cant handle short strings (Ex, (9, 'WTF')). I tried various ways to filter those out before substring is applied but none worked. There is something I don't understand in the way SQL-server pushes it predicates dow to the base tables. Pauls first query does not have these issues, so I think he deserves the credits
    – Lennart
    Jun 26 at 7:50
  • Hm. more and more (ahem..) interesting. I can modify my solution to work with any string with LEN() >= 1. However, when I use an empty string, TRY_CAST turns it into 0!!! This will work for the OP, but I can see (many) cases where 0 is a valid INT value, but CASTing blank to INT isn't! It's possible to put a CASE statement in there, but that's horrible! :-). This means the "hop" of 3 is no longer arbitrary. It chomps the first char of the string and then gets to the end of it ("). So, voilà - solved! :-) Jun 26 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.