artful at rogers.com
artful at rogers.com
Thu Dec 7 08:08:41 CST 2006
In my haste to ship the answer, I made a typo. The view is called INFORMATION_SCHEMA.COLUMNS.
I'll give it a go in SQL 2000. Same principle, just a little messier since the information is not gathered into one convenient view.
Arthur
----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Thursday, December 7, 2006 8:43:00 AM
Subject: Re: [dba-SQLServer] strip all leading / trailing spaces
>I'm assuming that you are using SQL 2005
That would be a bad assumption.
Sounds like you are on the track I need. As I am about 3 steps below pond
scum in the SQL abilities ranking, please keep me informed on this as it
gets closer to "just usable by pond scum".
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
artful at rogers.com
Sent: Thursday, December 07, 2006 8:24 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
_______________________________________________
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