[dba-SQLServer] strip all leading / trailing spaces

Michael Maddison michael at ddisolutions.com.au
Wed Dec 6 18:03:24 CST 2006

Hi John,

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

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

This would be done in a sproc.


Michael Maddison

DDI Solutions Pty Ltd
michael at ddisolutions.com.au
Bus: 0260400620
Mob: 0412620497
-----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

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
"ltrim(rtrim(data)) as SomeFieldName" kind of a thing but that means a
custom view for each table, manually edited to create these
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
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list