Remove empty and null values from table

szymon_k

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.

SruthiA

@szymon_k : Please find the sample solution below. The following query helped in filtering rows with varchar and int columns having NULL.

CREATE TABLE Employee1
(EmployeeID INT NOT NULL,
EmployeeName VARCHAR(50) NULL,
EmployeeSalary INT NULL
);

dbadmin=> select * from employee1;
EmployeeID | EmployeeName | EmployeeSalary
------------+--------------+----------------
1 | Rajendra |
1 | Ra |
1 | Ra,m |
7 | |
7 | |
(5 rows)

dbadmin=> select * from employee1 where employeeid not in (select employeeid from employee1 where EmployeeSalary is null and employeeName is null);
EmployeeID | EmployeeName | EmployeeSalary
------------+--------------+----------------
1 | Rajendra |
1 | Ra,m |
1 | Ra |
(3 rows)

dbadmin=>