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

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?

Thanks,
-=beeky

On Wed, Jan 21, 2009 at 1:19 PM, Wm.A.Stafford
<stafford@marine.rutgers.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."

On 2009-01-21, Wm.A.Stafford <stafford@marine.rutgers.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?

the sql to do this is

UPDATE tablename SET columnname=TRIM( columnname );

Do you really need a function?