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 <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!