JWColby
jwcolby at colbyconsulting.com
Wed Dec 6 17:47:37 CST 2006
>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
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