CODEWITHSUNDEEP
sqlfilteringsnowflake-cloud-data-platformblank-line
Agnieshka
Agnieshka

Reputation: 61

Filtering out empty strings in Snowflake

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?

Upvotes: 6

Views: 41901

Answers (3)

Mike Gohl
Mike Gohl

Reputation: 737

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

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

Upvotes: 6

Shantanu Kher
Shantanu Kher

Reputation: 1054

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

WHERE LENGTH(TRIM(COL_NAME)) > 0

Upvotes: 12

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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:]]'

Upvotes: 1

Related Questions