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

Re: removing leading and trailing blanks from every row in a table

From: Christopher Browne <cbbrowne(at)gmail(dot)com>
To: stafford(at)marine(dot)rutgers(dot)edu, pgsql-novice(at)postgresql(dot)org
Subject: Re: removing leading and trailing blanks from every row in a table
Date: 2009-01-21 19:31:30
Message-ID: d6d6637f0901211131l4db814cl8d45a124ced2faa4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Jan 21, 2009 at 1:19 PM, Wm.A.Stafford
<stafford(at)marine(dot)rutgers(dot)edu> wrote:
> I need a procedure to remove leading and trailing blanks from column of
> every row in a table.  I would like to pass the table name as a parameter
> and have the procedure do the rest.  Does something like this already exist?
>  Is it even possible?

There is a function that can help with this...

testdb=> select '|'|| btrim ('   foo', ' ') || '|';
 ?column?
----------
 |foo|
(1 row)

I wouldn't run it against every column, particularly as some columns
won't be text.

But you could certainly loop through a series of tables and columns
with queries like:
  update my_table set some_col = btrim(some_col, ' ') where some_col
is not null and btrim(some_col, ' ') <> some_col;

I wouldn't want to run this indiscriminately, as there is a risk of
this breaking uniqueness and failing.

I would instead want to do this selectively.
-- 
http://linuxfinances.info/info/linuxdistributions.html
George Burns  - "You can't help getting older, but you don't have to get old."

In response to

pgsql-novice by date

Next:From: Kasia TuszynskaDate: 2009-01-21 20:59:50
Subject: Re: PostgreSQL + PostGIS - "mining" FGDC metadata of GIS datasets
Previous:From: Wm.A.StaffordDate: 2009-01-21 18:19:27
Subject: removing leading and trailing blanks from every row in a table

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