Stripping empty space from all fields in a table?

NAVIGATION

CATEGORIES

Databases

REFERRENCE

If you have some good idea, Cilck and mail to us please...

LINKS

Home » Databases

Stripping empty space from all fields in a table?
6 answers - 280 bytes -
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
No.1 | | 552 bytes | |
You can use something like

select ltrim(string, ' ');

and

select rtrim(string, ' ');

Thanks,

Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

10/28/06, J B <jbwellsiv (AT) gmail (DOT) comwrote:

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
No.2 | | 786 bytes | |
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

(end of broadcast)
TIP 4: Have you searched our list archives?
No.3 | | 569 bytes | |
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-
(end of broadcast)
TIP 3: Have you checked our extensive FAQ?
No.4 | | 1055 bytes | |
Fri, 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) ||
')'
FRM
information_schema.columns
WHERE
table_name = 'person'
AND
data_type = 'character varying'
),
',
') ||
';
'
FRM
information_schema.tables
WHERE
table_schema NT IN ('pg_catalog','information_schema')
;

through psql.

Cheers,
D
No.5 | | 1167 bytes | |
Fri, 27, 2006 at 05:21:47PM -0700, David Fetter wrote:
Fri, 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?

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) ||
')'
FRM
information_schema.columns c
WHERE
table_name = t.table_name
AND
table_schema = t.table_schema
AND
data_type = 'character varying'
),
',
') ||
';
'
FRM
information_schema.tables t
WHERE
t.table_schema NT IN ('pg_catalog','information_schema')
AND
t.table_type = 'BASE TABLE'
;
No.6 | | 37 bytes | |
Worked perfectlythank you!

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

EMSDN.COM

Archive for 'Stripping empty space from all fields in a table?' by J B

Databases

Stripping empty space from all fields in a table?