[dba-SQLServer] strip all leading / trailing spaces

By coincidence, JC, I was writing about this subject for an upcoming article for my SQL Tips column at Tech Republic. It's not quite a stored procedure yet but it is kind of slick. I'm assuming that you are using SQL 2005. 

There is a system view called INFORMATION_SCHEMA.COLUMNS which (gasp) will return the names of the columns (plus some other stuff too). I then combined that output with a slick little trick that causes SQL to generate SQL. 

<code>
USE MyDatabase         -- substitute the name of your own DB
GO

SELECT 'Update MyTable SET ' + Column_Name + ' = Ltrim(Trim( ' + Column_Name + '))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ProductTypes'
AND Data_Type IN( 'char', 'varchar' )              -- add to this for data types of interest
</code>

This query returns a series of Update statements similar to this:

Update MyTable SET ProductTypeID = Ltrim(Trim( ProductTypeID))
Update MyTable SET ProductTypeName = Ltrim(Trim( ProductTypeName))


You could then take this output and wrap it in a CREATE PROCEDURE statement that accepts a parameter for the table name of interest.

hth,
Arthur


----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Wednesday, December 6, 2006 6:47:37 PM
Subject: Re: [dba-SQLServer] strip all leading / trailing spaces

>Are there more than 64,000 rows?

Oh yes.

In the case where I started, I had 21 files with 3 million rows each, 700
fields.  It is the sheer number of fields that prompts me to look for a
generic solution for this.  I am getting data from the same source, fixed
width, trailing spaces.  The fields match my original 700 fields (they are
the same source tables), but the exact fields in any given file vary.  The
number of records varies based on what matches they are looking for.

They may give me the name address stuff and fields 400-450 one time, another
time it might be name / address / Fields 125-178. In every case, the data in
all fields is padded.  It is simply impossible to process the fields in a
timely manner if I have to do ltrim(rtrim(FieldXXX)) crap on all the fields,
every time I get these files.

I wrote a program in Access (VBA) to take the data, which I exported out in
CSV format, and strip the spaces in code, and write the results back out to
a csv file.  It wasn't hard in VBA but it does require the export / strip /
import / merge sequence.  It would be nice to just do an update query that
was built up inside of a stored procedure or something that "discovered" the
field names, built up an update query with the ltrim(rtrim()) stuff and then
ran that query to just clean up the data 'in situ'.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Wednesday, December 06, 2006 5:55 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] strip all leading / trailing spaces

Hi John,

Are there more than 64,000 rows?

;-)

Dan Waters

-----Original Message-----
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