Remove empty and null values from table
I need to create generic script to remove any rows with empty or null values. The problem is, it has to work for both numeric and string columns. Moreover, in Vertica empty string is not treated as null. So I thought about simple:
SELECT col1 FROM table WHERE col1 IS NOT NULL and col1 != '';
This works fine unless column is INT, then I get an error. Other way is casting column to varchar:
SELECT col1 FROM table WHERE col1 is NOT NULL and CAST(col1 as VARCHAR) != '';
But casting like that is not efficient. I thought also about using ACII function, but again, for numeric values I get an error.
I have no idea what would be the most efficient way of doing this.