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]%'
2 Answers
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');
If all the records you want to detect come in addition to the end of the format that should be then you can use length.
Basically:
WHERE NOT len(column_name)=27
27 because your correct format 2020-12-26T12:12:11.215581Z has 27 characters.
