Search for string that start or finish with spaces and removing these spaces

Question

I have a table that some column have spaces, like this:

Column A
-----------
"text 1"
" text 2"
"text 3 "
" text 4 "

I want to do a select that return those columns with spaces (beginning or end)

" text 2"
"text 3 "
" text 4 "

Is that possible?

After search for them, How could I update those columns to remove the spaces?

Sounds like a job for a regular expression: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP — mlinth, Jan 20 '21 at 18:22

score 5 · Answer 1 · answered Jan 20 '21 at 18:22

5

I would use btrim():

select column_a
  from your_table
 where btrim(column_a) != column_a;

answered Jan 20 '21 at 18:22

Mike Organek

albertoivo · Accepted Answer · 2021-01-20T18:55:20.700

1

I solved my problem with REGEX:

select column_a
  from your_table
 where column_a ~* '(^\s+)*(\s+$)';

The problem with trim is that it removes only spaces with unicode U+0020. TABS and others will not be caught.

And to update the column:

UPDATE mytable
   SET col_a = substring(col_a, '\S(?:.*\S)*')
 WHERE col_a in (
    select col_a from mytable
     where col_a ~* '^\s+|\s+$'
);

edited Jan 20 '21 at 18:55

answered Jan 20 '21 at 18:31

albertoivo

regex is expensive. use comment from bruno caballo. – fukanchik Jan 20 '21 at 18:32
@fukanchik I agree with you. But `trim` does not work the way I want, the explanation is here: https://stackoverflow.com/a/22701212/9033414 If not using `trim` the other way would be `substring`. – albertoivo Jan 20 '21 at 18:41

2 Answers2