Skip site navigation (1) Skip section navigation (2)

Re: Stripping empty space from all fields in a table?

From: Steve Atkins <steve(at)blighty(dot)com>
To: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Stripping empty space from all fields in a table?
Date: 2006-10-27 19:47:29
Message-ID: DEDF90D3-3DA9-40D0-8AC9-FF678815B6D9@blighty.com (view raw or flat)
Thread:
Lists: pgsql-general
On Oct 27, 2006, at 12:39 PM, J B wrote:

> Guys,
>
> I have a table that has various fields that have whitespace in the  
> values. I'd like to roll through and strip the left and right  
> whitespace out of all fields that contain strings.
>
> Is there any easy way to do this?

UPDATE foo SET bar = btrim(bar) WHERE bar != btrim(bar)

should do it. That'll trim spaces - if you have a broader definition
of whitespace you should take a look at the docs for the btrim
function.

If this'll hit most of the rows on your table you probably want
to do a vacuum full (or a cluster) afterwards to recover all the
unused rows.

Cheers,
   Steve



In response to

pgsql-general by date

Next:From: Tom LaneDate: 2006-10-27 19:55:35
Subject: Re: Simple OUTER JOIN doubt
Previous:From: Shoaib MirDate: 2006-10-27 19:46:11
Subject: Re: Stripping empty space from all fields in a table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group