Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cptcrusty1
Starting Member
3 Posts |
 Posted - 2011-02-07 : 22:16:34
|
| Ok. I bet this is a toughy. In Oracle, I think I could do this, but I'm pretty rough in SQL Server.We are importing records from flat files that are literally hundreds of columns wide (SAP Data). We're going to analyze the data, eventually.Importing the Pipe-delimited flat files isn't the problem. Cleaning all the crud from around the column names isn't the problem either. It's after we do the import (and have cleaned up extra spaces) we still find leading and trailing spaces on what seems to be random columns. I want to run something that is going to change the column names to get rid of all the extra spaces... Kind of like the Trim or Clean functions in Excel.I don't want to manually do this over 100 times, for dozens of imports.Any ideas?ThanksCrusty.Always keep in mind the SQL you write today, might have to be deciphered by your replacement when you move on. Well documented code is well written code. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-08 : 06:07:58
|
Mmmmmmmmmmm I always wanted to be a SAP dba  To elaborate a little on Taras answer you could do something like this:declare @sql nvarchar(max) = ''select @sql = @sql + 'alter table ' + table_name + ' alter column ' + ltrim(rtrim(column_name)) + ' varchar(200) null' + CHAR(13)from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAME like 'a%'print @sql--exec sp_executesql @sql - LumbagoMy blog-> www.thefirstsql.com |
 |
|
|
cptcrusty1
Starting Member
3 Posts |
Posted - 2011-02-08 : 07:07:00
|
| Tara and Lumbago, great answers. Lumbago, your answer looks like it will affect all the columns with the letter 'a' at the beginning which would only affect columns starting with 'a', having trailing spaces. I have leading spaces too. Am I reading that correct? I'm answering this early in the morning with 1. no coffee, 2. no glasses... LOLAlways keep in mind the SQL you write today, might have to be deciphered by your replacement when you move on. Well documented code is well written code. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-08 : 07:43:18
|
Well, if it's only leading and trailing spaces you want to replace then change the where clause to where COLUMN_NAME like ' %' or COLUMN_NAME like '% 'I simply wrote my query to filter on "a" to get some hits in the database I was testing on, but I assumed you'd change the where to your liking  - LumbagoMy blog-> www.thefirstsql.com |
|
|
|
cptcrusty1
Starting Member
3 Posts |
Posted - 2011-02-08 : 08:59:28
|
| As Homer Simpson has been frequently quoted as saying.. "D'OH!" I should have seen that. Ordinarily, I'd say having a "like '%'" statement would drag down query performance.. but in this case, it's special...again... D'OH!Always keep in mind the SQL you write today, might have to be deciphered by your replacement when you move on. Well documented code is well written code. |
|
|
|
|
|
|
|
|
dynamicly change column names?