CODEWITHSUNDEEP
postgresql
albertoivo
albertoivo

Reputation: 409

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

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?

Upvotes: 2

Views: 1699

Answers (2)

albertoivo
albertoivo

Reputation: 409

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+$'
);

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

I would use btrim():

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

Upvotes: 6

Related Questions