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

Stripping empty space from all fields in a table?

From
"J B"
Date:
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

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

From
"Shoaib Mir"
Date:
You can use something like

select ltrim(string, ' ');

and

select rtrim(string, ' ');

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

On 10/28/06, J B <jbwellsiv@gmail.com> 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

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

From
Steve Atkins
Date:
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



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

From
Bricklen Anderson
Date:
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?

From
David Fetter
Date:
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!

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

From
David Fetter
Date:
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!

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

From
"J B"
Date:
Worked perfectly...thank you!