I have multiple tables in an Oracle schema that have columns with incorrect values:
- Number columns where the value is zero, instead of null.
- Text columns where the value is a space, instead of null.
I’m in the process of cleaning up the data. I will eventually create constraints on the columns to prevent bad data being entered in the first place. But before I do that, I need to find the columns that have existing problems and investigate them.
To start, I would like to produce a list of columns that have incorrect values.
- For each table in a schema, provide a list of columns that have zeros or spaces as values.
table_name column_name datatype value count —————————————————————————————————-————————————-———————- roads width number 0 500 sidewalks description varchar2 [space] 10000 sidewalks const_year number 0 2000
What would it take to produce a list like this in Oracle?
(Note: I’m a public works technician by day, not a database administrator. Layman’s terms would be appreciated.)