How can I convert a string with a date of various formats into one date field in one query?

Question

SQL

Good afternoon, there is a column of type text, in which the values ​​of dates. You need to convert the text to date format
But the problem is that the date format is presented in several variants, namely:

FIELD
mm/dd/yyyy
yyyy-mm-dd
dd.mm.yyyy

Those. no questions per format

SELECT TO_DATE(field, 'mm/dd/yyyy')
  FROM table

But what about when there are several of them, and so that the result fits in one column?

FIELD               FIELD
01/30/2020          01/30/2020
2020-01-30    ->    01/30/2020
30.01.2020          01/30/2020

Answer 1

I did it on the advice above through CASE, I didn’t know that inside it you can use regular expressions

TO_DATE(field,
         CASE
           WHEN regexp_like(field, '^\d{4}-\d{1,2}-\d{1,2}$') THEN 'yyyy-mm-dd'
           WHEN regexp_like(field, '^\d{1,2}\/\d{1,2}\/\d{4}$') THEN 'mm/dd/yyyy'
           WHEN regexp_like(field, '^\d{1,2}.\d{1,2}.\d{4}$') THEN 'dd.mm.yyyy'
           ELSE NULL
         END
         ) AS field