[dba-SQLServer] strip all leading / trailing spaces

Hi John,

It certainly is possible...  I have seen scripts that do that sort of
thing.
Can't find anything at the moment but if you google/BOL
INFORMATION_SCHEMA 
should get you started.

Basically, the INFORMATION_SCHEMA views let you interogate the database
schema,
use a cursor to loop through the tables/fields build up your string,
execute it.

This would be done in a sproc.

HTH

Michael Maddison

DDI Solutions Pty Ltd
michael at ddisolutions.com.au
Bus: 0260400620
Mob: 0412620497
www.ddisolutions.com.au 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Thursday, 7 December 2006 8:35 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] strip all leading / trailing spaces

Folks,
 
Does anyone have a "something" in SQL Server that will take the name of
a table, figure out all of the field names in the table, and return a
data set with all of the data stripped of leading and trailing spaces?
 
I have tables where the data waqs brought in to SQL Server from a fixed
width file, padded right (mostly) with spaces.  Thus the data is
represented inside the table as fixed width with spaces to the right of
any real data.
I need to get the data out, strip the spaces (both leading and trailing)
and the write that data back into the same or another table, doesn't
matter which to me.  I know how to do this by creating a view and then
use
"ltrim(rtrim(data)) as SomeFieldName" kind of a thing but that means a
custom view for each table, manually edited to create these
"ltrim(rtrim("
constructs.  I assume if I knew how to do udfs or stored procedures that
you could go get the field names of the fields and dynamically build a
sql string.
 
Has anyone seen such a thing?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com