[dba-SQLServer] strip all leading / trailing spaces

JC, try the following, just to get a look:

SELECT * FROM
INFORMATION_SCHEMA.TABLES

SELECT * FROM
INFORMATION_SCHEMA.COLUMNS

There are several views. 

Susan H. 

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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


--
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.16/552 - Release Date: 11/26/2006