How can I trim a text array in PostgreSQL?

Question

Is it possible to trim a text array in PostgreSQL? If so, how?

I want to get something like:

    select trim(myTextArrayColumn) from myTable;

where myTextArrayColumn is of type text[].

Example column values:

    {"someData1 ", "someData2 "}

    {" someData3 "}

score 4 · Accepted Answer · 2012-07-29 09:04:35Z

4

This should do it:

select array_agg(trim(e))
from (
  select row_number() over () as rn, 
         unnest(myTextArrayColumn) e
  from mytable
) t
group by rn

edited Jul 29, 2012 at 9:04

answered Jul 29, 2012 at 7:40

user330315

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

1 Comment

Yaakov Bressler Over a year ago

Works smoothly! (I'm using postgres 13.x)

Mikhail I. · Accepted Answer · 2012-08-01 02:01:54Z

2

try this, i think suitable for your

select string_to_array(replace(array_to_string(arrColumn, '::'), ' ', ''), '::') from myTable

note: 1. all spaces will be removed 2. your values must does not contains '::', if contains use other delimeter

answered Aug 1, 2012 at 2:01

Mikhail I.

3462 silver badges3 bronze badges

1 Comment

Andrew Over a year ago

To avoid replacing all spaces, you can trim with this modification, select string_to_array(replace(replace(array_to_string(arrColumn, '::'), ' ::', '::'), ':: ', '::'), '::') from myTable

Collectives™ on Stack Overflow

How can I trim a text array in PostgreSQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related