Convert different string date, into one single date format In mysql

Question

I have an column create_date in my sql table, datatype is varchar(225). My problem is, in that column values are in different date formats Please check the attached Image

I want to create another column with constant_date_format using create _date column, is there way?

I have tried with mysql str_to_date function:

STR_TO_DATE(create_date, '%m/%d/%Y') 

it is working if create_date column have same date format.

Answer 1

You can case when with regular expression

SELECT CASE WHEN create_date REGEXP [0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] THEN STR_TO_DATE(create_date, '%m-%d-%Y') WHEN create_date REGEXP Format THEN STR_TO_DATE(create_date, '%m/%d/%Y') ELSE create_date END AS create_date FROM TABLE_NAME

https://www.guru99.com/regular-expressions.html