MySQL-> many date format as varchar to date format

Question

I need to change a column that contains many date formats (now is varchar) like

• 09-10-09
• 19/01/2009
• 19/01/09, etc

into a column with date format (year,month,day). I try this query but brings me error.

SELECT CASE 
  WHEN data_rep LIKE "%/%/%"
  THEN str_to_date( data_rep, '%Y/%m/%d' )
  WHEN data_rep LIKE "%.%.%"
  THEN str_to_date( data_rep, '%Y.%m.%d' )
  WHEN data_rep LIKE "%-%-%"
  THEN str_to_date(data_rep, '%Y-%m-%d')
  ELSE CAST(data_rep AS DATE)
END AS data_good,
FROM ....

Error:

SQL Error(1064): You have an error in your SQL synta.Check the manual.... for the right syntax to use near '(data_rep,'%Y/%m/%d') when data_rep like "%-%-%"

Thanks!