Filtering out empty strings in Snowflake

user_13537732 0
Viewed 12916

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?

blank-line snowflake-cloud-data-platform filtering sql
3 Answers

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

WHERE LENGTH(TRIM(COL_NAME)) > 0
user_114209840

You need to know what "empty" row means. Most likely an empty string:

where lastname > ''

If you need to be sure that there is at least one letter you can use:

where lastname regexp '[a-zA-Z]'

or:

where lastname regexp '[[:alpha:]]'
user_11440350

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

where Coalesce(trim( lastname ), '') <> ''

user_121452800
Related