Обсуждение: Stripping empty space from all fields in a table?

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

You can use something like

select ltrim(string, ' ');

and

select rtrim(string, ' ');

Thanks,
--------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )

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

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-

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

Remember to vote!

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!

Worked perfectly...thank you!