Use SQL fuzzy matching to verify whether a field is a date format

Recently you need to verify that a field in the Data warehouse is converted to a date format, such as timestamp format ' 2016-05-03 23:21:35.0 ',

However, DB2 does not support regexp_like (matching) functions, so you need to rethink other options.

The final use of the most common like to fuzzy matching, although less than the regular match so accurate, but also enough.

Ideas:

An underscore represents a character, and that ' 2016-05-03 23:21:35.0 ' can be expressed as ' ____-__-__-__.__.__.______ '.

Of course, this method is rather stupid, do not recognize whether it is a number or a letter or a character, of course, the better way is to write a UDF (custom function) to achieve regular matching.

SELECT emp_id, Timestamp,rtrim (CAST (TIMESTAMP as CHAR)) Dtfrom TEST. Emptabwhere RTRIM (CAST (TIMESTAMP as CHAR ())) Not like ' ____-__-__-__.__.__.______ ' with UR;

Use SQL fuzzy matching to verify whether a field is a date format