What’s the best way to check if the VARIANT TYPE with JSONs with value similar to TIMESTAMP_NTZ has correct format?
For example, this is the correct format that I would like to have
2020-12-26T12:12:11.215581Z
but there are times when it looks different in database, like this
2021-11-26T12:12:11.215581Z[UTC]
I would like to detect records which are in a different format than the reference.
I tried with simple LIKE, but it omits formats that could be different than this.
LIKE '%[UTC]%'
Advertisement
Answer
You may use a regular expression to check the equality of the desired format. I will extend this answer if the rdbms is known.
[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{6}Z
MySQL & SQLite:
SELECT * FROM table
WHERE timestamp not REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{6}Z';
Oracle:
SELECT * FROM table
WHERE not REGEXP_LIKE(timestamp, '[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{6}Z');