How to check TIMESTAMP format in SQL?

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]%'

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