CODEWITHSUNDEEP
sqlpostgresql
Slim Kode
Slim Kode

Reputation: 27

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

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.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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))

Upvotes: 1

Related Questions