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?

score 10 · Answer 1 · answered Jul 01 '20 at 18:57

10

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

WHERE LENGTH(TRIM(COL_NAME)) > 0

answered Jul 01 '20 at 18:57

Shantanu Kher

1,014
1
8
14

2

Careful with the sister version: `where length(trim(colname)) = 0`, it will also evaluate to false with a null value! – Adrien Brunelat Sep 15 '22 at 15:20

score 2 · Answer 2 · edited Nov 09 '22 at 21:26

2

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

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

edited Nov 09 '22 at 21:26

JayRizzo

3,234
3
33
49

answered Jul 01 '20 at 18:45

Mike Gohl

627
4
7

score 0 · Answer 3 · answered Jul 01 '20 at 18:28

0

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

answered Jul 01 '20 at 18:28

Gordon Linoff

1,242,037
58
646
786

Filtering out empty strings in Snowflake

3 Answers3