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

Remember to vote!