I need a query in postgres that returns the length of arrays but without taking empty values into consideration

Question

so given the table:

id     | names
===============
1        {John, , Wayne}
2        {Luke, Harold, }
3        {Bill}
4        {Will, , }

They don't have a standard and some values may come empty ( for example {Will, , }). I tried:

SELECT array_length(names, 1)
FROM nameTable

But I get this:

names
======
3
3
1
3

and I want it to return:

names
======
2
2
1
1

So I need something which gives me the length only of the populated fields (empty spaces like ' ') shouldn't be counted.

Gordon Linoff · Accepted Answer · 2019-12-11 12:21:02Z

1

You can remove the NULL values and then count:

array_length(array_remove(names, NULL), 1)

For one-dimensional arrays, I find that cardinality() is convenient:

cardinality(array_remove(names, NULL))

answered Dec 11, 2019 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

This is it. it works! But instead of Null I put ' '. Thank you!

@SlimKode . . . I assumed the unseen value was NULL, but you can put in any value.