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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 replace blank spaces

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

Posted - 2006-10-03 : 09:08:46
THIS IS DYNAMIC SQL AGAIN!
Did you learn nothing from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72882 ?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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?
Go to Top of Page

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 int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'

select @minVoter = min(id_voter),
@maxVoter = max(id_voter)
from vwMyHugeView


while (@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 Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 09:56:45
See also:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Replace+multiple+spaces,with+single+space

Kristen
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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---joeNak
my name is--joeNak
my name is----joe nak


result-field
---------------------
my name is joeNak
my name is joenak
my name is joenak



the ---- are spaces.
Go to Top of Page

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)
As

Begin
Declare @Clean varchar(8000)

Set @Clean = LTrim(RTrim(@StrToClean))

While CharIndex(' ', @Clean, 0) <> 0
Set @Clean = Replace(@Clean, ' ', ' ')

Return @Clean
End

3. Update the super-view.

--typo
Go to Top of Page

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 table
2) 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 data

Now the data is somewhat normalized and much, much easier to maintain!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

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.
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -