Filtering out empty strings in Snowflake

Question

I have a very simple task which is turning out to be impossible. I have a column that has strings but also has blanks where it supposed to be a word (those are not NULLs they are just empty strings). For example:

LastName1,
EmptyRow,
LastName2,
EmptyRow,
EmptyRow,
LastName3...

Since empty rows are not NULLs, IS NOT NULL function is not working.

In the end result, I just need to filter out all rows that actually have values in them and get rid of all the empty rows. Can someone please give a suggestion?

Answer 1

You can filter out these records by applying LENGTH function to it.

WHERE LENGTH(TRIM(COL_NAME)) > 0

Answer 2

If there is a mix of single spaces, no spaces and nulls, I uses something like this

WHERE COALESCE(TRIM( lastname ), '') <> ''