Extract a string in Postgresql and remove null/empty elements

Question

I Need to extract values from string with Postgresql But for my special scenario - if an element value is null i want to remove it and bring the next element 1 index closer.

e.g. 
 assume my string is: "a$$b"

If i will use

select string_to_array('a$$b','$')

The result is:

{a,,b}

If Im trying

SELECT unnest(string_to_array('a__b___d_','_')) EXCEPT SELECT ''

It changes the order

1.d
2.a
3.b

order changes which is bad for me.

I have found a other solution with:

select array_remove( string_to_array(a||','||b||','||c,',') , '')
from (
select
split_part('a__b','_',1) a,
split_part('a__b','_',2) b,
split_part('a__b','_',3) c
)  inn

Returns

{a,b}

And then from the Array - i need to extract values by index e.g. Extract(ARRAY,2)

But this one seems to me like an overkill - is there a better or something simpler to use ?

user330315 · Accepted Answer · 2019-02-25 13:51:13Z

2

You can use with ordinality to preserve the index information during unnesting:

select a.c
from unnest(string_to_array('a__b___d_','_')) with ordinality as a(c,idx)
where nullif(trim(c), '') is not null
order by idx;

If you want that back as an array:

select array_agg(a.c order by a.idx)
from unnest(string_to_array('a__b___d_','_')) with ordinality as a(c,idx)
where nullif(trim(c), '') is not null;

answered Feb 25, 2019 at 13:51

user330315

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

Collectives™ on Stack Overflow

Extract a string in Postgresql and remove null/empty elements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related