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?
You could use a regex approach here and match on the pattern ^[0-9]{4}-[0-9]{2}-[0-9]{2}$:
SELECT *
FROM yourTable
WHERE dimension_id ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';