I have a table that contains some columns. Unfortunately it was not designed in the best way and REST API that writes to this table was not designed good too, but I need some fast solution. In column dimension_id (text datatype) I have next values: 90% of rows contains date like 2024-01-01, but others contains string like 9391 or 78417. I dont want to delete these rows now (a lot of data, a big project, it takes a long time to understand and rewrite, maybe it’s a valid values but need other column), but I need to filter them, because views created on this table fall when u try to cast and filter this column.

So I need to grab all rows where dimension_id contains date pattern.

I tried with

select * from table
where TO_DATE(dimension_id, 'YYYY-MM-DD') IS NOT NULL

But it didn’t help. How to do this?