Author |
Topic |
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 09:06:53
|
i have a field with blank spaces.i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.any input will be appreacited.i have something like this but its not working. declare @field varchar(50) declare @minVoter int declare @maxVoter int declare @tableName varchar(20)set @tablename = '00001' -- select ad_str1 from [00170] select @minVoter = min(id_voter), @maxVoter = max(id_voter) from quotename(@tablename) while (@minVoter <= @maxVoter) begin select @field = ad_str1 from quotename(@tablename) where id_voter = @MinVoter update [00170] set ad_str1 = replace(@field, ' ', ' ') select @minVoter = min(id_voter) from quotename(@tablename) where id_voter > @minvoter end |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 09:10:19
|
peso, is there anyway to do it without using dynamic sql? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 09:14:15
|
Yes. If you create the "superview" as described in the post linked above.declare @field varchar(50)declare @minVoter intdeclare @maxVoter intdeclare @tableName varchar(20)set @tablename = '00001'select @minVoter = min(id_voter), @maxVoter = max(id_voter)from vwMyHugeViewwhile (@minVoter <= @maxVoter) begin select @field = ad_str1 from myhugeview where id_voter = @MinVoter and tablename = @tablename update [00170] set ad_str1 = replace(@field, ' ', ' ')-- this will update ALL rows in [00170]. -- Where is the WHERE? select @minVoter = min(id_voter) from vwMyHugeView where id_voter > @minVoter end Peter LarssonHelsingborg, Sweden |
|
|
Kristen
Test
22859 Posts |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 10:23:45
|
kristen, i tried that link. But people are just making fun of others. So they are not seriously helping.thats why i opened a new thread. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 10:35:37
|
"i tried that link."So what's wrong / your difficulty with that approach then?Kristen |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-10-03 : 10:37:57
|
I'm confused are you trying to update one field in 500 tables or 500 fields in 500 tables? |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 10:44:54
|
1 field in 1 tables. I have 500 tables. That makes a total of 500 fields.The fields have 2,3, even 4 blank spaces. I want to replace them with 1 blank space.fields-------------------my Names is---joeNakmy name is--joeNakmy name is----joe nakresult-field---------------------my name is joeNakmy name is joenakmy name is joenakthe ---- are spaces. |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-10-03 : 11:23:16
|
Ok.1. I'd take Peso's suggestion and create a super-view to update.2. Create a function to do cleaning:Create Function [dbo].[fn_CleanSpaces] (@StrToClean varchar(8000))Returns varchar(8000)AsBegin Declare @Clean varchar(8000) Set @Clean = LTrim(RTrim(@StrToClean)) While CharIndex(' ', @Clean, 0) <> 0 Set @Clean = Replace(@Clean, ' ', ' ') Return @CleanEnd3. Update the super-view.--typo |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 13:54:49
|
I don't get it.You have 500 tables, with only 1 column in each of them?Here is a really valuable tip for you!1) Create a new table2) Add two columns, TableName and Fields.3) Move all previous data from the 500 fields to the new table, with the name of the table as column dataNow the data is somewhat normalized and much, much easier to maintain!Peter LarssonHelsingborg, Sweden |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-03 : 14:34:57
|
Peter: Heaven forbid! That is way, way, WAY too easy. NO challenge. What are you thinking?[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 14:50:25
|
peso, the tables have around 20 fields each. I only need to update one field. Replace the multiple spaces, remember. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-04 : 00:44:36
|
Funketekun: Then create a new table with 20+1 columns!DonAtWork: I was stunned by the above stated fact...Peter LarssonHelsingborg, Sweden |
|
|
|