Records greater than epoch timestamp using only LIKE operator
5
votes
2
answers
1832
views
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.
Asked by Elite298
(61 rep)
Jun 24, 2022, 08:56 PM
Last activity: Jun 26, 2022, 12:54 PM
Last activity: Jun 26, 2022, 12:54 PM