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

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

On Fri, Oct 27, 2006 at 03:39:21PM -0400, 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?

If you're really and want to hit all your tables, run the output of
the following:

SELECT
    'UPDATE
    ' ||
    quote_ident(table_schema) ||
    '.' ||
    quote_ident(table_name) ||
'
SET
    ' || array_to_string(ARRAY(
    SELECT
        quote_ident(column_name) ||
        ' = trim(' ||
        quote_ident(column_name) ||
        ')'
    FROM
        information_schema.columns
    WHERE
        table_name = 'person'
    AND
        data_type = 'character varying'
    ),
    ',
    ') ||
    ';
'
FROM
    information_schema.tables
WHERE
    table_schema NOT IN ('pg_catalog','information_schema')
;

through psql.

Cheers,
D
-- 
David Fetter  http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

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

On Fri, Oct 27, 2006 at 05:21:47PM -0700, David Fetter wrote:
> On Fri, Oct 27, 2006 at 03:39:21PM -0400, 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?

Oops.  The code I originally posted was wrong.  Here's a better one.

Cheers,
D

SELECT
    'UPDATE
    ' ||
    quote_ident(t.table_schema) ||
    '.' ||
    quote_ident(t.table_name) ||
'
SET
    ' || array_to_string(ARRAY(
    SELECT
        quote_ident(c.column_name) ||
        ' = trim(' ||
        quote_ident(c.column_name) ||
        ')'
    FROM
        information_schema.columns c
    WHERE
        table_name = t.table_name
    AND
        table_schema = t.table_schema
    AND
        data_type = 'character varying'
    ),
    ',
    ') ||
    ';
'
FROM
    information_schema.tables t
WHERE
    t.table_schema NOT IN ('pg_catalog','information_schema')
AND
    t.table_type = 'BASE TABLE'
;

-- 
David Fetter  http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

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

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?
> 
> Thanks!
> 
> JB

"trim" will strip the whitespace from both sides. ltrim and rtrim are 
front/back specific.

select '-'||trim('  asdf  ')||'-';
  ?column?
----------
  -asdf-

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

Stripping empty space from all fields in a table?

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

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

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