Stripping empty space from all fields in a table?

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!