I am having a column with data type as CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL but there are chances the same column will hold date value as well. We need to check if the column has valid date or not? If valid date then we need to store the same or else we need to default it. I want to try this without using udf but the other method i tried is to cast to check for yyyy or mm or dd as below:
Quick thought, you could always do a left outer join to the sys_calendar.calendar view and update the data to the default where col1 is null...
How does the date look like, is there a fixed format?
Leading zeroes for month and day?
Any other characters before/after the date?
There is no fixed format. Currently that column has 0, 50, AC etc...as values. Might be in future we will get date values. I am assuming that should be in yyyy-mm-dd format.
IF its yyyy-mm-dd, regexp_similar can be used in td14
This gives 1, if true.
Just with case (returns valid date or null in case invalid date)